cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
campelliann
Helper II
Helper II

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?

1 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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

4 REPLIES 4
campelliann
Helper II
Helper II

First and foremost thank you for your help.

@Greg_Deckler @rajendran 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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

rajendran
Community Champion
Community Champion

@campelliann @rajendran 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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.