Dax statement to display data in one column based on most recent entry or max date

DAX
4

Hi @Heather.Bisher

2023 Stats help file JB.pbix (244.5 KB)

Try this (inspired from @Harsh 's answer here)

 Last Agent Value by Class = 
 VAR vTable =
 SUMMARIZE(
     '2023 Stats From Data',
     '2023 Stats From Data'[Entry Date],
     '2023 Stats From Data'[Class Number ],
     '2023 Stats From Data'[Current # of Agents]
 )
 
 VAR _FTable =
 FILTER(
     vTable,
 
     VAR _Max_Date_Each_Class =
     CALCULATE( MAX( '2023 Stats From Data'[Entry Date] ),
         ALLEXCEPT( '2023 Stats From Data', '2023 Stats From Data'[Class Number ] ) )
 
     VAR _Results =
     '2023 Stats From Data'[Entry Date] = _Max_Date_Each_Class
 
     RETURN
     _Results )
 
 VAR _Results =
 SUMX(
     _FTable ,
     '2023 Stats From Data'[Current # of Agents] )
 
 RETURN
     _Results

Some things to consider with your data though:

  1. Some classes have nothing for the latest date, such as class 106-23. Changing the _Max_Date_Each_Class variable to this:
     VAR _Max_Date_Each_Class =
     CALCULATE( MAX( '2023 Stats From Data'[Entry Date] ),
         ALLEXCEPT( '2023 Stats From Data', '2023 Stats From Data'[Class Number ] ),
         '2023 Stats From Data'[Current # of Agents] <> BLANK())
  • Would correct this. For 106-23, it would then be using 3rd Feb which has 17 instead of 6th Feb, if that’s what you require.
  1. Some classes such as 101-23 have multiple entries for the latest date. The DAX supplied is currently summing all values for the latest date, so for that one it is showing 31 as there are records saying 15 and 16 for the latest date. Is that correct or is there a tiebreaker to seperate those 2 records?