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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PaisleyPrince
Helper II
Helper II

Obtaining latest value for maximum date in dax

Hi,

I'm trying to write a measure which will calculate the latest status of an item , by location and by the maximum date applicable. Sample data as per below. Any guidance would be much appreciated.

thanks

Scott

PaisleyPrince_1-1712861180450.png

 

 

1 ACCEPTED SOLUTION

@PaisleyPrince This seems to work for me. See PBIX attached beneath signature:

Latest Status = 
    VAR __LatestDate = MAX('Table'[Date])
    VAR __Result = MAXX(FILTER('Table',[Date] = __LatestDate), [Status])
RETURN
    __Result


Follow on LinkedIn
@ 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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
PaisleyPrince
Helper II
Helper II

Hi @Greg_Deckler , I've tried it but it doesnt work as expected. For example Item 2 - S01 gives the wrong result. Sample data in text is as follows

ItemlocationStatusDate
1S01Current 01/02/2024
1S02Discount01/03/2023
1S03Notrequest22/07/2023
1S01Discount30/11/2023
1S02Notrequest01/07/2023
1S03Current 18/05/2022
2S01Current 17/02/2022
2S02Notrequest01/12/2023
2S03Discount01/12/2023
2S01Discount15/06/2023
2S02Current 22/03/2024
2S03Notrequest22/01/2024
3S01Discount11/02/2023
3S02Notrequest11/02/2024
3S03Current 11/05/2023
3S01Current 16/05/2023
3S02Discount12/03/2024
3S03Discount12/02/2023

@PaisleyPrince This seems to work for me. See PBIX attached beneath signature:

Latest Status = 
    VAR __LatestDate = MAX('Table'[Date])
    VAR __Result = MAXX(FILTER('Table',[Date] = __LatestDate), [Status])
RETURN
    __Result


Follow on LinkedIn
@ 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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Community Champion
Community Champion

@PaisleyPrince Lookup Min/Max - Microsoft Fabric Community



Follow on LinkedIn
@ 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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler , thanks for this pattern. My only question is that i'm looking up a status rather than a numerical value - can you please advise how would i amend the pattern accordingly?

@PaisleyPrince Is largely the same. Can't test because you didn't provide your sample data in text and no idea about your visual but should be something like:

Measure =
  VAR __Item = MAX('Table'[Item])
  VAR __Location = MAX('Table'[Location])
  VAR __Table = FILTER( ALLSELECTED('Table'), [Item] = __Item && [Location] = __Location )
  VAR __MaxDate = MAXX( __Table, [date] )
  VAR __Result = MAXX( FILTER( __Table, [date] = __MaxDate ), [Status] )
RETURN
  __Result

 



Follow on LinkedIn
@ 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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.