Understanding Earlier Function in DAX

The DAX EARLIER function is often explained as – “It returns the current row value of a column”

Which is correct.. but partially!

The simplistic definition above does not fully explain the nitty gritties of EARLIER. In this post, I’ll do my best to show you the finer workings of EARLIER. We’ll also take a look some use cases and how to use EARLIER in a Column and in a Measure.

You’re going to stay here for a bit so get a coffee before you start 🙂

I would highly recommend to use Variables (VAR) instead of the Earlier Function. 

This is tricky, wanna grab the Video first ?

EARLIER Syntax

The function accepts 2 inputs

1. Column Name
2. Number which is optional

Now at the risk of losing you at the start I am going to say something difficult but if you stick around you’d understand every word by the end of this post.

 The Earlier Function returns the value of the Current Row in the Column specified and the EARLIER function at-least needs to have 2 nested ROW Contexts.

If you din’t understand anything.. that’s okay, you should just remember 2 things and move on.

• Earlier will return the value of the current row in a column.
• To use the Earlier we’ll need to create 2 nested row contexts – and that is the most unexplained part.

Consider this Sales Table

Question – Create a Column to count the instance for each date, in other words a COUNTIF on Date Column

• So I’d write 2 since 2nd-Jan-2011 appears twice
• And number 3 for 6th-Jan-2011.. you get the idea!

Let’s solve this using EARLIER

Using EARLIER in a Column

If start your formula like this = EARLIER ( Sales[Date] ) you’d won’t be able to write the formula since that is deemed invalid. So let’s start differently – consider the following calculated column

COUNTIF Dates Column = 
COUNTROWS(
    FILTER(
        Sales,
        EARLIER(Sales[Date]) = Sales[Date]
    )
)

Solved. But the question that has personally bothered me for a long time is that..

1. Why do I need the FILTER Function to be able to work with EARLIER and
2. Why din’t the EARLIER function work when written at the start of the calculated column ?

Remember what I said about EARLIER – It needs 2 nested ROW Contexts. Time to explain this

Step 1 – When you created a Calculated Column in the Sales table you are technically working in a ROW Context and are writing the formula in each row of that Table.

Step 2 – When you use the FILTER function in each row of the SalesTable, a nested second row context is created that pulls the entire Sales table (again) in each row of the outside Sales Table

Step 3 – Each Date in the second row context (inner Sales Table loop, which is inside the FILTER function) is then compared with the current row Date of the first filter context (outer Sales Table loop).

So EARLIER in essence means the current row of the immediate outer loop.

and that’s how the earlier function works.

Using EARLIER in a Measure

To be able to meaningfully explain the use case of EARLIER in a Measure consider this Case.

1. Along with the sales table I have a Region Expenses Table.
2. Which has a one to many relationship with the Sales Table. Sales being on the many side, linked with Region Column.

I have to allocate the Budget Value equally to all the rows of the respective region in the Sales table. For instance, if I filter my sales table for Region = “New Delhi”, I get 476 rows, hence the allocation will be = 20,000/476 for each row of the Sales table.

Question – Create a Measure that allocates the Budget Value in the Sales Table.

Consider this Measure

Budget Allocated Measure using Earlier = 
SUMX(
    Sales,
    1 / COUNTROWS(
        FILTER(
            ALL(Sales),
            EARLIER(Sales[Region]) = Sales[Region]
        )
    ) * RELATED('Region Expenses'[Budget])
)

My logic will be somewhat like this

1. In each row of the Sales Table
2. Find the allocation % for each row i.e. 1/476 (eg. for New Delhi)
3. Then multiply that allocation % with the Budget Value i.e. 20,000 x 1 / 476 (for New Delhi)

And since to be able to work with the EARLIER Function I need to have 2 nested ROW Contexts, I start by using SUMX and then use FILTER to find the count of total rows that belong to each Region.

An alternative approach (measure) using Variables could be like this

Budget Allocated Measure using Variables = 
SUMX(
    Sales,
    VAR CurrentRegion = Sales[Region]
    RETURN
        1 / COUNTROWS(
            FILTER(
                ALL(Sales),
                CurrentRegion = Sales[Region]
            )
        ) * RELATED('Region Expenses'[Budget])
)

Another measure to do the same thing using could be done using ALLEXCEPT

Budget Allocated Measure using AllExcept = 
SUMX(
    Sales,
    DIVIDE(
        1,
        COUNTROWS(
            CALCULATETABLE(
                Sales,
                ALLEXCEPT(Sales,'Region Expenses'[Region])
                )
            )
        ) * RELATED('Region Expenses'[Budget])
)

All doing the same thing 🙂 See the Results

Using EARLIER in more than 2 nested Row Contexts

Remember at the start of this post, I mentioned that earlier has 2 parts..

.

1. Column Name
2. Number – What is that for? Let’s Explore

Consider that I added 2 more tables with no relationships to any other tables

First Table

Second Table

Notice the column name (Value) is the same in both the above tables and to induce further ambiguity, I’ll add another column to my Sales Table called “Value”. In this column I’ll write my name throughout all the rows.

I then create a test column and write the following DAX

Test = 
CONCATENATEX(
    'First Table',
    CONCATENATEX(
        'Second Table',
        EARLIER([Value], 2) & '-' & [Value]
    )
)

Now notice the results

A couple of interesting (or mundane) things to note..

1. The number 2 allows you go outside 2 loops and fetch the current row value of the outer most Sales Table.
2. Since there are only 2 nested loops, a number > 2 would result in an error. 1 is by default so we never write that 🙂
3. You can also modify the EARLIER part and instead write EARLIEST( [Value] ) which would do the same thing of fetching the current row value of the outer most loop.
4. You can also avoid writing the number input by prefixing the name of the table before the column name.

Since the column name was kept the same to induce ambiguity, I on purpose din’t write the preceding table name to help you understand (or probably confuse) with the number input 😀

You should probably watch the Video again!

EARLIER Function – Conclusion

Chances are that you either understood everything that I just explained or none of it. Just remember this – You need to have 2 nested row contexts to be able to work with the EARLIER function, be it in a Column or in a Measure.

Let me know in the comments if you have questions, I’d be glad to help.

More on DAX!

1. Calculate Function Quick Tip – Calculate for Non Blanks
2. Financial Year Calendar Table in Power BI
3. Slab or Tiered Calculations using DAX
4. Sort by Column Examples
5. Calculate SAMEPERIODLASTYEAR values for partial year