Microsoft Office Tutorials and References
In Depth Information
The EARLIER function in DAX provides exactly this behavior: getting data from the previous
row context. Any column referenced in an EARLIER function call returns the value of that
column, ignoring the current row context.
EARLIER Parameters and the EARLIEST Function
The EARLIER function has a second optional parameter that specifies how many
evaluation passes have to be discarded. This is useful when you have multiple, iterating,
nested functions, changing the row context multiple times. If the second parameter is
omitted, it defaults to 1, which indicates the previous row context.
A similar function, named EARLIEST, has a single parameter, which is the column name.
It returns the column value out of any evaluation pass (in other words, it is the first row
context of the whole DAX expression). You can get the same result of EARLIEST by passing
-1 to the second parameter of EARLIER.
So because you need the Date value of the current row before the execution of the FILTER
statement, you can use the EARLIER syntax:
EARLIER( Sales[Date] )
So the right DAX definition of the YTD calculated column is the following one:
= SUMX( FILTER( Sales, Sales[Date] <= EARLIER( Sales[Date] )
&& YEAR( Sales[Date] ) = YEAR( EARLIER( Sales[Date] ) ) ),
EARLIER and Filter Context
The EARLIER function operates only on row contexts. There are no DAX functions (other
than CALCULATE) that can return an expression value for a different (previous) filter
context because filter context is not modified when you use this approach.
In this chapter, you have seen how the evaluation context works and how it can be
manipulated when you use the CALCULATE function in a DAX expression. The evaluation context can
be defined by a filter context and a row context. Each of these contexts has an effect on DAX
expressions and they are also affected by the presence of relationships between tables in a