Marco Russo recently blogged about the usage of dates arguments in a row context. The measure he presents as producing “wrong results”, actually produces the results I expected. For this reason, I had a hard time understanding the motivation behind his article and decided to write a follow-up in case anyone else missed his very subtle explanations about context in DAX expression. If you have not done it yet, I recommend you read his entry, before and/or after reading mine.

So, suppose you have a table called *OrderDate* with a *Date* column and one row for each day, and define the following measures:

LastDay : = LASTDATE(OrderDate[Date])

NumLastDay := COUNTROWS(

FILTER(OrderDate,

OrderDate[Date] = [LastDay]

)

)

If you evaluate the measure *NumLastDay* for the month of January, for example, you will get 31 as a result. As already explained by Marco, FILTER iterates the *OrderDate* table: It creates a row context for every row of the *OrderDate* table present in the current filter context. The measure *LastDay* will be evaluated for every single row(day). Hence, the expression *OrderDate[Date] = [LastDay]* will always return *TRUE*.

Contrary to Marco, I was expecting this. Also, you can get the same result if you define *LastDay* as

LastDay := MAX(OrderDate[Date])

That is why I did not immediately get his point, and what was so special about the LASTDATE function.

However, LASTDATE and MAX behave differently when used directly within the FILTER formula:

DirectCall – MAX := COUNTROWS(

FILTER(OrderDate,

OrderDate[Date] = MAX(OrderDate[Date])

)

)

will return 1 when evaluated for the month of January. The evaluation of OrderDate[Date] within the MAX expression is done for the current filter context, disregarding the row context created by the FILTER expression.

On the contrary,

DirectCall – LASTDATE := COUNTROWS(

FILTER(OrderDate,

OrderDate[Date] = LASTDATE(OrderDate[Date])

)

)

will return 31 when evaluated for the month of January.

The filter expression

OrderDate[Date] = LASTDATE(OrderDate[Date])

actually behaves like the following expressions

OrderDate[Date] = CALCULATE(MAX(OrderDate[Date]))

OrderDate[Date] = CALCULATE(MAXX(OrderDate,[Date]))

OrderDate[Date] = MAXX(CALCULATETABLE(OrderDate),[Date]))

OrderDate[Date] = CALCULATE(LASTDATE(OrderDate[Date]))

OrderDate[Date] = LASTDATE(CALCULATETABLE(OrderDate[Date]))

where the CALCULATE and CALCULATETABLE parts create a new filter context from the current row context, for the evaluation of OrderDate[Date].

That is the point Marco Russo makes in his article about the LASTDATE function. He presents two solutions to calculate a measure for the last day within the current filter context (see his article).

Here is mine, by the way

=CALCULATE(

COUNTROWS(OrderDate),

LASTDATE(OrderDate[Date])

)