Obtaining latest value for maximum date in dax

[PaisleyPrince]

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]

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

Item	location	Status	Date
1	S01	Current	01/02/2024
1	S02	Discount	01/03/2023
1	S03	Notrequest	22/07/2023
1	S01	Discount	30/11/2023
1	S02	Notrequest	01/07/2023
1	S03	Current	18/05/2022
2	S01	Current	17/02/2022
2	S02	Notrequest	01/12/2023
2	S03	Discount	01/12/2023
2	S01	Discount	15/06/2023
2	S02	Current	22/03/2024
2	S03	Notrequest	22/01/2024
3	S01	Discount	11/02/2023
3	S02	Notrequest	11/02/2024
3	S03	Current	11/05/2023
3	S01	Current	16/05/2023
3	S02	Discount	12/03/2024
3	S03	Discount	12/02/2023

[Greg_Deckler · 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

[Greg_Deckler]

@PaisleyPrince Lookup Min/Max - Microsoft Fabric Community

[PaisleyPrince]

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

[Greg_Deckler]

@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