Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Finding latest value based on date

Hi everyone!

 

I have a data set (CheckUP) which contains all the check up meetings I have had with several employees. It is a monthly thing so I collect the answers and then refresh the source file to update the existing data set. Currently all my graphs show all date points so that I can track the line and see how things are going. However, I have about 7-8 tables which contain details associated to each date. 

 

To make it much easier to read, I have filtered every table based on the latest date. However, every time I have a new meeting I have to manually adjust the filter to show the newest latest date. 

 

What I would like to create is something like a measure that looks for the latest date for each person and applies this to the filter (or it might be the filter). 

The data set looks something like this:

Person nameGeneral viewGeneral view detailsSubjectDate
Person A7GoodPlanned visit01-01-2020
Person A4BadPlanned call04-02-2020
Person A9Very goodPlanned call12-03-2020
Person B2Very badPlanned visit18-02-2020
Person B5NeutralPlanned call16-03-2020
Person C7GoodPlanned visit16-05-2020

 

So for the General view I have a graph that shows the General view over time, but for the General view details I want to only see the most recent date in the table and have it update every time it finds a new latest value for that person.

 

I tried using measure: Calculate (Sum (CheckUP[general view details]), LASTDATE(CheckUP[Date])) but it doesn't seem to work. Might have done something wrong here.

3 REPLIES 3
v-eachen-msft
Community Support
Community Support

Hi @Anonymous ,

 

In your dataset table above, I find that the "CheckUP[general view details]" is text type. If you use SUM() function on this field, it will return an error.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

I did not notice @v-eachen-msft, thank you for clarifying that issue. Would you by any chance know how I could change this so it would work with my dataset? 

Anonymous
Not applicable

So I managed to get a filter that returns true/false and based on that I can filter out the falses and keep only the latest date, however I have 2 types of subject: visit and call and I want to know the latest date for both subject types.

 

This column is what I am currently using:

LatestDate = report1588859888847[End Date] =
CALCULATE(
MAX(report1588859888847[End Date]),
FILTER(ALL(report1588859888847), report1588859888847[Person name]=EARLIER(report1588859888847[Person name]))
)
 
Is there a way somehow to make it check for the subject type too or have another column added that also checks for subject type so I can use that as a filter/slicer?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors