topic Re: Obtaining latest value for maximum date in dax in DAX Commands and Tips

Obtaining latest value for maximum date in dax

PaisleyPrince — Thu, 11 Apr 2024 18:47:01 GMT

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

Re: Obtaining latest value for maximum date in dax

Greg_Deckler — Thu, 11 Apr 2024 18:51:37 GMT

PaisleyPrince Lookup Min/Max - Microsoft Fabric Community

Re: Obtaining latest value for maximum date in dax

PaisleyPrince — Thu, 11 Apr 2024 18:59:52 GMT

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?

Re: Obtaining latest value for maximum date in dax

Greg_Deckler — Thu, 11 Apr 2024 19:04:53 GMT

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

Re: Obtaining latest value for maximum date in dax

PaisleyPrince — Fri, 12 Apr 2024 13:44:01 GMT

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

Re: Obtaining latest value for maximum date in dax

Greg_Deckler — Sun, 14 Apr 2024 13:45:56 GMT

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