Can EARLIER be used in DAX measures?

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:

image

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]
)

 

image

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 Smile

So lets see what happens if you try to use the same expression as a measure.  You get this error:

image

“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] )
)
)

image

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.

image

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.

Advertisements

14 Responses to Can EARLIER be used in DAX measures?

  1. Gerhard Brueckl says:

    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])))

    • javierguillen says:

      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…

  2. Great post Javier. I’ve had very similar issues getting to grips with this. Your post definitely helps greatly. Thanks

  3. Smithicus says:

    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.

  4. Smithicus says:

    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…

  5. Pingback: Calculate and Earlier context help

  6. Pingback: Earlier() function

  7. judgedi says:

    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!

    • javierguillen says:

      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.

  8. jyothi says:

    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

  9. javierguillen says:

    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

  10. Pingback: WMP Blog » Favorite Quick & Easy DAX: Calculate Moving Averages with EARLIER

  11. cmiller says:

    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!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: