Forum Discussion
LastDate vs MAX (date)
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 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.
4 Replies
It is explained in detail here :
Difference between LASTDATE and MAX for semi-additive measures in #DAX - SQLBI
Thanks
Rajcampelliann 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.
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.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.
