Comparing data (dates) from the same table with same ID, and displaying the max date

[Anonymous]

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

[v-yuta-msft · Accepted solution]

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