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:
SumX (
Filter ( Table1, Table1[date] <= Earlier ( Table1[date] ) ),
Table1[amount]
)
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:
RunningTotal:= CALCULATE(
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:
CALCULATE(
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.
Javier Guillén 
Hi Javier,
I was struggeling with a similar problem recently when i was calculating RunningTotals over non-date columns
for testing i create the running totals on a date-column first:
ValueCum:=CALCULATE
(SUM(Facts[Value]);
ALL(‘Date'[Day]);
DATESBETWEEN(‘Date'[Day];Facts[FirstDate];Facts[LastDate]))
where FirstDate and LastDate where defined as follows:
FirstDate:=CALCULATE(FIRSTDATE(‘Date'[Day]); ALL(‘Date’))
LastDate:=LASTDATE(‘Date'[Day])
this worked just fine as i could use DATESBETWEEN
when i changed the calculation to a non-date column i had to use FILTER instead of DATESBETWEEN
see my first approach below – i had the same problem as you as i tried to use EARLIER
ValueCumNumeric:=CALCULATE(
SUM(Facts[Value]);
FILTER(
ALL(DateNumeric);
DateNumeric[DayNumber] <= EARLIER(DateNumeric[DayNumber])))
so i tried the same approach as before creating measures for MaxDateNumber as
LastDateNumber:=MAX(DateNumeric[DayNumber])
and the calculation
ValueCumNumeric:=CALCULATE(
SUM(Facts[Value]);
FILTER(
ALL(DateNumeric);
DateNumeric[DayNumber] <= Facts[LastDateNumber]))
this resulted in all rows showing the same values
from my understanding the reason for this behavior was that by using a seperate measure to calculate LastDateNumber, a CALCULATE gets wrapped around it and it is calculated in the current FILTER-context of ALL(DateNumeric)
by specifying the LastDateNumber-Calcluation directly in the FILTER, this problem was solved:
ValueCumNumeric:=CALCULATE(
SUM(Facts[Value]);
FILTER(
ALL(DateNumeric);
DateNumeric[DayNumber] <= MAX(DateNumeric[DayNumber])))
Hi Gerhard! Thanks for your comment 🙂
As you point out, the engine uses an implicit CALCULATE when referring to measures; with the resulting effect of the currently iterated row context propagating as filter context, giving results that may not be immediately obvious – all of which is at the heart of the famous “CALCULATE wall” 🙂
on the hand, EARLIER is one of those functions that are briefly explained yet require quite a bit of effort to understand. Its evaluation context is worth examining even if its rarely used as part of a DAX measure…
Great post Javier. I’ve had very similar issues getting to grips with this. Your post definitely helps greatly. Thanks
Thanks David! I appreciate your comments, and visiting my blog 🙂
I dont get it just yet and I am hoping you can explain:
The following table illustrated how I think the measure works:
It 1: Filter(All) is at row 1, Inner Filter is at row 1:
EARLIER( Table1[1-1-2011] ) True
It 1: Filter(All) is at row 1, Inner Filter is at row 2:
EARLIER( Table1[1-1-2011] ) True
Etc…
It 2: Filter(All) is at row 2, Inner Filter is at row 1:
EARLIER( Table1[1-2-2011] ) False
It 2: Filter(All) is at row 2, Inner Filter is at row 2:
EARLIER( Table1[1-2-2011] ) True
It 2: Filter(All) is at row 2, Inner Filter is at row 2:
EARLIER( Table1[1-2-2011] ) True
Wouldnt this mean that Row with value 1-1-2011 woudl have a running total equal to the grand total of Amount.
I must be missing something… I would greatly appreciate it if you (or anybody else) would be so kind as to explain this to me.
opps I see some of the text of my explenation vanished …
It 1: Filter(All) is at row 1, Inner Filter is at row “1:
“EARLIER( Table1[1-1-2011] ) True”
It 1: Filter(All) is at row 1, Inner Filter is at row 2:
“EARLIER( Table1[1-1-2011] ) True”
etc…
Pingback: Calculate and Earlier context help
Pingback: Earlier() function
Hi Javier,
Great blog – you have a lot of useful info and this specifically is a topic for which not much is to be found on the Net.
However, I believe the explanation is a bit misleading. You say “Doesn’t SUMX generate an iteration, just like FILTER does?” but it is not the SUMX which provides the outer context. In fact, SUMX doesn’t know which table it operates on (and thus which is the current context) before the full completion of FILTER.
What provides the outer context is PowerPivot which is looping through all rows of the table in order to evaluate the calculated column. You can check this by doing an EVALUATE of the formula in DAX Studio. Without having a calculated field, it will fail with “EARLIER/EARLIEST refer to an earlier row context which doesn’t exist.” which will show you that the SUMX by itself is not creating an outer context.
The fact that it works as a column, but does not work as a measure is logical. In other words, there is no loop when value is evaluated because a value is at the cross-section of a column and row and thus the row context is meaningless (or rather to say incomplete).
Keep up the good work!
Hi Judgedi
Thanks for your comments! Indeed, the intention is show how the behavior can be non-intuitive. SUMX, by definition, is an iterative function – just as FILTER. But as you point out, FILTER is evaluated first, before SUMX, hence no nested loop is created.
However, both of them operate under row context (given their iterative nature). When using EARLIER, though, there must be nested row context to make it work. Calculated columns, by default, operate under row context hence the need for only one other iterator to generate nested loops.
Measures, by default, operate under filter context which explains the need to use two iterators to generate the same effect.
Please help. I would really appreciate it. I have a table with 3 columns and 4th column should be a calculated column based on the data in 3 columns.
SO Date PO Arr (calculated field)
123 2/1/2013 PO1 123
234 4/1/2013 PO1 123
345 5/1/2013 PO1 123
Arr = For the SO that has same PO #, Arr will be the earliest date of SO#
Could you please help me how to do this using DAX. Your help is greatlly appreciated. I will keep checking this webpage for any answers. Thanks again
Hi Jyothi,
The earliest date or the earliest PO? In the example you give above, the calculated column does not show a date but a number 123.
If what you need is the earliest date as a calculated column (not calculated field), you can achieve this by using the following expression:
=CALCULATE( MIN( Table1[Date] ), ALLEXCEPT( Table1, Table1[PO] ) )
with the following result:
SO Date PO CalculatedColumn1
123 2/1/2013 0:00 PO1 2/1/2013 0:00
234 4/1/2013 0:00 PO1 2/1/2013 0:00
345 5/1/2013 0:00 PO1 2/1/2013 0:00
455 4/1/2013 0:00 PO2 4/1/2013 0:00
Pingback: WMP Blog » Favorite Quick & Easy DAX: Calculate Moving Averages with EARLIER
Javier,
Just wanted to say thanks for this blog post!! I’m learning DAX on the fly for a project I’m working on have been stuck on this exact problem for days. Figured there was a simple solution and I was just missing something so I’ve been banging my head against the wall for days. Never would have got there on my own.
Thanks again!!