johnny_doyle
New Member
- Joined
- Apr 13, 2017
- Messages
- 3
Hi
I am looking for help with a DAX Measure. I have a list of milestones that might change each month. I am looking to do two things.
- Return the latest status value of the milestone,
- Count the number that match that status value, e.g.how many are now Green, Amber, etc.
My data looks a bit like this.
With a related table
I am looking for help with a DAX Measure. I have a list of milestones that might change each month. I am looking to do two things.
- Return the latest status value of the milestone,
- Count the number that match that status value, e.g.how many are now Green, Amber, etc.
My data looks a bit like this.
| date | reference | status |
01/05/2020 | milestone1 | Green |
01/05/2020 | milestone2 | Green |
01/05/2020 | milestone3 | Red |
01/05/2020 | milestone4 | Amber |
01/05/2020 | milestone5 | Green |
01/05/2020 | milestone6 | Green |
01/05/2020 | milestone7 | Red |
01/05/2020 | milestone8 | Amber |
01/05/2020 | milestone9 | Green |
01/05/2020 | milestone10 | Red |
01/06/2020 | milestone1 | Amber |
01/06/2020 | milestone2 | Amber/Red |
01/06/2020 | milestone3 | Amber/Red |
01/06/2020 | milestone4 | Amber/Red |
01/06/2020 | milestone5 | Amber |
01/06/2020 | milestone6 | Amber/Red |
01/06/2020 | milestone7 | Green |
01/06/2020 | milestone8 | Green |
01/06/2020 | milestone9 | Amber/Red |
01/06/2020 | milestone10 | |
01/07/2020 | milestone1 | |
01/07/2020 | milestone2 | |
01/07/2020 | milestone3 | Amber |
01/07/2020 | milestone4 | Amber/Red |
01/07/2020 | milestone5 | Green |
01/07/2020 | milestone6 | Green |
01/07/2020 | milestone7 | Amber/Red |
01/07/2020 | milestone8 | |
01/07/2020 | milestone9 | |
01/07/2020 | milestone10 |
With a related table
| Status | Value |
| Blank | 0 |
| Green | 1 |
| Green/Amber | 2 |
| Amber | 3 |
| Amber/Red | 4 |
| Red | 5 |
| Delivered | 6 |
| Cancelled | 7 |
