EARLIER Function (DAX)
Returns the current value of the specified column in an outer evaluation pass of the mentioned column.
EARLIER is useful for nested calculations where you want to use a certain value as an input and produce calculations based on that input. In Microsoft Excel, you can do such calculations only within the context of the current row; however, in DAX you can store the value of the input and then make calculation using data from the entire table.
EARLIER is mostly used in the context of calculated columns.
A column or expression that resolves to a column.
(Optional) A positive number to the outer evaluation pass.
The next evaluation level out is represented by 1; two levels out is represented by 2 and so on.
When omitted default value is 1.
The current value of row, from column, at number of outer evaluation passes.
EARLIER succeeds if there is a row context prior to the beginning of the table scan. Otherwise it returns an error.
The performance of EARLIER might be slow because it theoretically, it might have to perform a number of operations that is close to the total number of rows (in the column) times the same number (depending on the syntax of the expression). For example if you have 10 rows in the column, approximately a 100 operations could be required; if you have 100 rows then close to 10,000 operations might be performed.
In practice, the xVelocity in-memory analytics engine (VertiPaq) performs optimizations to reduce the actual number of calculations, but you should be cautious when creating formulas that involve recursion.
To illustrate the use of EARLIER, it is necessary to build a scenario that calculates a rank value and then uses that rank value in other calculations.
The following example is based on this simple table, ProductSubcategory, which shows the total sales for each ProductSubcategory.
The final table, including the ranking column is shown here.
Bottles and Cages
Tires and Tubes
One way to obtain a rank value for a given value in a row is to count the number of rows, in the same table, that have a value larger (or smaller) than the one that is being compared. This technique returns a blank or zero value for the highest value in the table, whereas equal values will have the same rank value and next value (after the equal values) will have a non consecutive rank value. See the sample below.
A new calculated column, SubCategorySalesRanking, is created by using the following formula.
= COUNTROWS(FILTER(ProductSubcategory, EARLIER(ProductSubcategory[TotalSubcategorySales])<ProductSubcategory[TotalSubcategorySales]))+1