Forum Discussion
Comparing data (dates) from the same table with same ID, and displaying the max date
Hi,
I was wondering if it's possible to compare dates within the same table with same ID, but the catch is that there is an additional column that display the status. For instance, here's a table:
| ID | Status | Date |
| 1 | Running | 2/3/2018 |
| 1 | Stopped | 4/4/2018 |
| 2 | Running | 3/24/2018 |
| 2 | Stopped | 1/2/2018 |
----------------------------------------------------
The results I'm expecting to see is this:
| ID | Status | Date (highest date) |
| 1 | Stopped | 4/4/2018 |
| 2 | Running | 3/24/2018 |
Is there a way I can do this? A group by with ID would work, but the status column part would not being showing. Maybe using M language or DAX or something from the GUI. Could you show me a solution? I recently started using PowerBI at work, so I still have a lot to learn.
Thanks
Hi bcwan,
You can create a measure using DAX formula like below:
Date(highest date) = CALCULATE(MAX(Table1[Date]), ALLEXCEPT(Table1, Table1[ID]))
Regards,
Jimmy Tao
1 Reply
Hi bcwan,
You can create a measure using DAX formula like below:
Date(highest date) = CALCULATE(MAX(Table1[Date]), ALLEXCEPT(Table1, Table1[ID]))
Regards,
Jimmy Tao