Forum Discussion
Pull latest Report by Date
Hello,
I am trying to figure out how to acheive the following. I have googled and tried several different measures but so far not successful
I have a table named Project Status with the following fields (there are more but this is an example)
| Project | Health | Scope Health | Status Date |
| Project A | Green | Green | 8/1/2024 |
| Project A | Red | Green | 8/8/2024 |
| Project A | Yellow | Yellow | 8/15/2024 |
| Project B | Green | Green | 8/8/2024 |
| Project B | Red | Red | 8/15/2024 |
| Project C | Green | Green | 8/15/2024 |
I would like to pull the latest report based on the status date field and return all fields in the row. Measures I have tried have pulled The latest for each health, but I just wanted the latest report.
Expected Output
| Project | Health | Scope Health | Status Date |
| Project A | Yellow | Yellow | 8/15/2024 |
| Project B | Red | Red | 8/15/2024 |
| Project C | Green | Green | 8/15/2024 |
You can simply aggregate the date by latest, but DAX is needed to get the latest health text:
Last Health = CALCULATE(MAX('Table'[Health]), LASTDATE('Table'[Status Date]))You can check my setup below:
3 Replies
vicky_Super User
You can simply aggregate the date by latest, but DAX is needed to get the latest health text:
Last Health = CALCULATE(MAX('Table'[Health]), LASTDATE('Table'[Status Date]))You can check my setup below:
hey thank you for the reply. Using that code I get an error - A single value for column "Health" in table project status cannot be determined.
Also will this bring in additional fields? Example, I have scope health, budget health, schedule health
actually that worked, I typed something wrong. THank you so much!
