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

DAX
1

I am trying to write a Dax for a New Measure to display or filter the most recent entry for a Class (this is entered many times) and the Current class Number ( again entered many times and can change) but needing the most recent number.

Here is a look at My Data

HeatherBisher_0-1677101452163.png

as you can see I may have a class (115-23) with the Current # of agents entered several times I need to display only the most recent number of agents for each class Number.

I have this but it only gets part of the way I cant get past the if Statement if the expression is true…

Is most recent =

var CurrentClassNumber= ‘Form Data’[Class Number ]

var mostRecentDate = CALCULATE(MAX(‘Form Data’[Entry Date]),ALL(‘Form Data’),‘Form Data’[Class Number ] = CurrentClassNumber)

return

IF(‘Form Data’[Entry Date] = mostRecentDate,“”,“0”)

Any help would be great I am a relitivly new user comeing from Excel and hitting road blocks.

2

Hi @Heather.Bisher,

Welcome to the community and thank you for posting your question here! :smile:

I noticed you didn’t provide a PBIX file or a dummy working file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • A clear explanation of the problem you are experiencing
  • A mockup of the results you want to achieve
  • Your current work-in-progress PBIX file
  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Not completing your data may sometimes cause delays in getting an answer.

Thank you!

3

2023 Stats help file.pbix (252.4 KB)

image
image916×680 37.7 KB

Please see the attached Files

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

Hello @Heather.Bisher

Did the response from @Heather.Bisher help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION.

Thank you

6

Hello @Heather.Bisher

Just following up if the response above help you solve your inquiry.
If it did, please mark his answer as the SOLUTION.

We’ve noticed that no response was received from you on the post above. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

7

Hi @Heather.Bisher

Due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,.

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!

8

@jeanrose87 Sorry I have been out of the office, I will give this a look today,

9

The Tie breaker on this would be the “Entry Time” column.