Can EARLIER be used in DAX measures?
February 6, 2012 14 Comments
EARLIER is a DAX function that acts exclusively on row context, and its purpose is to create a reference to a column value on an outer loop of the evaluation of the expression. It is commonly used in calculated columns during nested row by row iterations.
One of the things that I have been wondering for about a year now (an eternity in this digital era!) is if it would be possible to use it in a measure. An example would perhaps make this more clear: suppose you want to use EARLIER to calculate running total values on a table like this one:
I know, there are special functions like TOTALYTD that can be used for this purpose, but it is always a good idea to explore other calculation options for the pure fun of learning (Yes, I’m a DAX geek). If you use EARLIER in a DAX calculated column like the one defined below, the Tabular model is able to compute the running total without a problem:
Filter ( Table1, Table1[date] <= Earlier ( Table1[date] ) ),
This is easy enough. For each row iteration in the in-memory table, we create a new filter that grabs all the rows with a date prior or equal to the one currently in context in the outer loop of the nested operation. In other words, the calculation defines a new loop – an inner loop – that can then invoke the value of [date] on the outer loop and in this way dynamically filter the table passed to the SUMX function. The effect is that for each row, we are able to add all the values up until the one currently in context.
Can you use the same formula as part of a measure? You can’t. At least not in the exact same way. And here is where I have been stuck for a while now, until Marco Russo ( blog | twitter ) and Alberto Ferrari ( blog | twitter ) helped me with some extra guidance (Thanks so much guys!!!). In addition the famous CALCULATE wall, there seemed to be an EARLIER wall as well
So lets see what happens if you try to use the same expression as a measure. You get this error:
“EARLIER/EARLIEST refer to an earlier row context which doesn’t exist.” Hmm. You might wonder, why? Doesn’t SUMX generate an iteration, just like FILTER does? And given the existence of these two loops, shouldn’t we assume a nested iteration has been created?
Not really. Marco and Alberto explained to me the fact that FILTER evaluates first, and by the time SUMX’s row context is being evaluated FILTER has already returned a table, hence the two contexts do not interact with each other. In that sense, EARLIER cannot be used.
If we really want to use EARLIER as part of a measure, we must introduce nested iterations of row context that interact with each other. In other words, the evaluation must happen for each row for each row.
So how can we recreate the running total calculation as a measure and using EARLIER? Here is what I came up with:
SUM( Table1[amount] ),
FILTER( ALL(Table1) ,
SUMX( FILTER( Table1, EARLIER( Table1[date] ) <= Table1[date] ), Table1[amount] )
The first thing to notice is the SUMX expression is very similar to the one used in the calculated column, with one exception: Instead of using the expression below as in the calculated column
Table1[date] <= EARLIER( Table1[date] )
I used the following one in the measure:
EARLIER( Table1[date] ) <= Table1[date] )
Lets see why. I introduced an outer loop by wrapping SUMX with another FILTER function, which in turn has cleared the existing filter context by invoking the ALL function. When this outer FILTER iterates across all values of the table, the inner FILTER uses a reference to the prior row context through the use of EARLIER and then selects only those rows in that have a date up to the one in current context in the outer loop. How is it different from the use in the calculated column? The difference is that as a measure we are really filtering the table passed to the FILTER in the outer loop, not the inner loop FILTER as in the calculated column expression.
The other thing to notice is that the inner FILTER didn’t remove any existing filters in the filter context. By passing the table without the use of the ALL function, context has been refined rather than expanded. However, as the outer FILTER did in fact remove filters by invoking the ALL function. The resulting interaction of filters allows the calculation to evaluate for the current day in context but is still able to operate over a all rows existing in the table up to the current day.
And finally, we have the use of the SUMX aggregate. It is very interesting that in this calculation that aggregate is irrelevant, in fact I would get the same running sum total if I where to use the following:
SUM( Table1[amount] ),
FILTER( ALL(Table1) ,
COUNTROWS( FILTER( Table1, EARLIER( Table1[date] ) <= Table1[date] ) )
(Notice the use of COUNTROWS instead of SUMX)
This is because the aggregate in this calculation is really just a placeholder, the actual evaluation context manipulation happens as a consequence of the intersection of filters on the two nested FILTER functions, which generates the required context to enable the running total to occur. And this is the filter context that the outermost function – CALCULATE – uses when evaluating the SUM of [amount] (In other words, it is this aggregate the one that actually matters in this expression).
Interestingly, the grand total generated with the DAX measures is correct as shown in the screenshot below: it reflects the last running value. On the other hand, if we do this as a calculated column and then place it in a pivot table, the grand total will incorrectly aggregate the values instead of showing the last one.
The use of nested row contexts is definitely an complex topic, probably not suited for self-service BI – but one that SSAS developers need to address in order to generate advanced calculations in the Tabular model.