[DAX] Earlier

EARLIER is not an easy-to-understand DAX function for Power BI users. I will use some examples to show you how to use EARLIER function.

What is EARLIER?

• Syntax: EARLIER(<column>, (number))
• The purpose of the function is to create a reference to a column value on an outer loop of the evaluation of the expression.
• EARLIER is mostly used in the context of calculated columns. That is, EARLIER is commonly used in the physical tables in your data model.
• The parameter (number) is optional.

This is the thinking process when I see EARLIER in a function.

EARLIER(Column) = the value in the Column.
(1) EARLIER(Date) = the date value in the Date Column
(2) EARLIER(Product) = the product value in the Product Column

Scenarios to use EARLIER function

Here are some situations that EARLIER would come in handy.
(1) Running Total (Yes, we discussed this in the previous article.) This is another way to execute it.
(2) How many times an item shows in a column

Scenario 1: Running Total (Cumulative Total) by Date

• Step 1. Since we are calculating the running total by date, we need to know the current date value of each row. Therefore, we use “EARLIER(Date)” to get the current date value.
• Step 2. To get the running total, we need to decide if the date value of the row is earlier than the specific row.
• Step 3. Use a dynamic table with a filter condition to get the running total

EARLIER is commonly used in the Calculated Column.

Scenario 2: How many times a specific date shows in a column

Assuming we would like to know how many times the date “1/1/2021” shows in the table. Apparently, “1/1/2021” shows 5 times (and every single date shows 5 times as well) since we have 5 products with its respective data.
Most importantly, how do we use DAX to get the number?

• Step 1. To get the current date value of each row. Therefore, we use “EARLIER(Date)” to get the current date value.
• Step 2. To get the running total, we need to decide if the date value of the row is earlier than the specific row.
• Step 3. Use a dynamic table with a filter condition to get the running total
• Step 4. Use COUNTROWS to count the number of occurrences of a date

COUNTROWS & EARLIER

Use EARLIER in a DAX measure

Can we use EARLIER is a DAX measure? The answer is yes! However, we seldom see EARLIER in a DAX measure. Why?

Here are some reasons.
(1) Running Total for MTD, YTD
DAX function TOTALMTD / TOTALYTD can be used for this purpose, and they are more intuitive. We will discuss the two functions in the future.

(2) You can’t type exactly the same formula above as part of a measure.
If you do so, you will see the error “EARLIER/EARLIEST refer to an earlier row context which doesn’t exist.”

The Error Message if you type an EARLIER in a measure without

The reason is that the function FILTER evaluates first, and by the time SUMX row context is evaluated, FILTER has already returned a table, hence the two contexts do not interact with each other. With that being said, EARLIER cannot be used.

To make it simple, EARLIER needs to have a table only for its use, and the table is used only as a placeholder.

Use EARLIER in a measure

Below you can see the results are the same.

Conclusion

EARLIER is a useful function if you are familiar with it. Here we talk about the common situations that EARLIER would be useful.