Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
campelliann
Post Patron
Post Patron

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
campelliann
Post Patron
Post Patron

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.