LastDate vs MAX (date)

[campelliann]

Hi,

So I was using Max (date) successfully to get the cummulative revenue in 2021 with a filter like date< max(date) on a measure. The max(date) with the filter context returns the correspondent month in the chart.

I tried the same thing with the lastdate, but it seems it ignores the filter context giving me in every month the revenue of the Lastdate(which is september).

I thought these functions would be similar.

Any ideas on why there is a difference?

[campelliann]

First and foremost thank you for your help.

@Greg_Deckler @Anonymous the measure I am using is this: 

Consumo Baseline YTD =

calculate(sum(Valores_Finais[Total]),filter(all('Z_Calendário Slicer'),'Z_Calendar Slicer'[YearMonth]<=MAX('Z_Calendar Slicer'[YearMonth])),filter(all(Valores_Finais),Valores_Finais[Type]="Baseline"))

I am using this on a chart, where the months are in x axis (january-september) with Z_calendar slicer [YearMonth] field. What I do not get is why the Lastdate does not work  in this case to have an cumulative YTD revenue and MAX does- I read that its a table (not a scalar), but LASTDATE is supposed to consider the filter context, so I  do not get why I always get the revenue of September with the LASTDATE.

[Greg_Deckler · Accepted solution]

@campelliann If you have it working, I'd walk away and declare victory. What does the LASTDATE version of that look like? This?

calculate(sum(Valores_Finais[Total]),filter(all('Z_Calendário Slicer'),'Z_Calendar Slicer'[YearMonth]<=LASTDATE('Z_Calendar Slicer'[YearMonth])),filter(all(Valores_Finais),Valores_Finais[Type]="Baseline"))

I don't see how that would work since you would be trying to compare a scalar to a table. And if it is this:

calculate(sum(Valores_Finais[Total]),filter(all('Z_Calendário Slicer'),LASTDATE('Z_Calendar Slicer'[YearMonth])),filter(all(Valores_Finais),Valores_Finais[Type]="Baseline"))

Then you would only get the last (biggest) [YearMonth] and would lose the < part.

[Anonymous]

It is explained in detail here :

Difference between LASTDATE and MAX for semi-additive measures in #DAX - SQLBI

Thanks
Raj

[Greg_Deckler]

@campelliann @Anonymous The only correction I would make to that article is that you can use MAX in a table context versus scalar by simply doing:

{ MAX('Dates'[Date]) }

In that respect, it is identical then to what LASTDATE returns. In other words, LASTDATE is a utterly useless function.