Forum Discussion
Advocate IIObtaining 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 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
5 Replies
- Greg_Deckler
Community Champion
PaisleyPrinceGreg_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
- 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
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