Power Pivot Principles: The A to Z of DAX Functions – EARLIER

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at EARLIER. 

The EARLIER function

The EARLIER function is one of the filter functions in DAX where it returns the current value of the specific column in an outer evaluation pass of the mentioned column (a nice and clear definition for you!).  The EARLIER function is quite useful for nested calculations where you want to use a certain value as an input and produce calculation using data from the entire table.  The EARLIER function is mostly used in the context of calculated columns.  It employs a very simple syntax:

where:

• column: this is required and, represents a column or expression that results in a column
• number: this is optional and, represents a positive number to be outer evaluation pass.  The next evaluation level out is represented by one [1], two levels out is represented by two [2] and so on.  When number is omitted, it will return the default value of one [1].

It should be noted:

• if there is a row context before the table scan starts, the EARLIER function is successful; otherwise, an error is returned
• the performance of the EARLIER function may be slow since, in theory, it may need to execute close to the whole number of rows (in the column) times the same number of operations (depending upon the syntax of the expression).  For instance, if the column has 10 rows, then 100 operations may be necessary; if the column contains 100 rows, then close to 10,000 operations may be necessary
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

To illustrate the use of the EARLIER function, consider the following example:

Here, we have the following Sales table.  First, we will create an aggregation measure for SalesAmount then we will use this aggregation along with the EARLIER function to create a cumulative amount for each date:

Next, we move to the Power Pivot data model to create a calculated column with the following DAX code: 

This DAX code will return a cumulative SalesAmount by date:

The following steps describe the method of calculation in more detail.

• the EARLIER function gets the value of Sales[OrderDate] for the current row in the table
• in this case, because the process is starting, it is the first row in the table
• EARLIER(Sales[OrderDate]) evaluates to 1 January 2013, the current row in the outer loop
• the FILTER function now returns a table where all rows have an order date less than 1 January 2013 (which is the current value for EARLIER)
• the CALCULATE function then sums the SalesAmount
• the calculated column formula moves to the next row and repeats the above steps
• these steps are repeated until the end of the table is reached.

The EARLIER function will always get the value of the column prior to the current table operation.  If you need to get a value from the loop before that, set the second argument to two [2].

Assuming you have wrapped your head around this, let’s move on.  If we use the same code in the calculated column to create a new measure, we will now get an error message:

There is a workaround if you want to create a measure instead of calculated column for an EARLIER function:

This DAX code help us replicate the same result we have in the previous example using calculated column for our measure.  You can create a Pivot Table to view the result of the measure:

As we can see the result here is identical to the result using the calculated column in Power Pivot.

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.