Forum Discussion
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 name | General view | General view details | Subject | Date |
| Person A | 7 | Good | Planned visit | 01-01-2020 |
| Person A | 4 | Bad | Planned call | 04-02-2020 |
| Person A | 9 | Very good | Planned call | 12-03-2020 |
| Person B | 2 | Very bad | Planned visit | 18-02-2020 |
| Person B | 5 | Neutral | Planned call | 16-03-2020 |
| Person C | 7 | Good | Planned visit | 16-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
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.
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?
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?
