How to calculate latest status based on date?

[slav84]

Hello, 

I have a table with over 100k rows that looks like this:

StatusTime	OrderNumber	Status
3/22/2021	1	New
3/23/2021	1	InProcess
3/24/2021	1	Shipped
3/25/2021	2	New
3/26/2021	2	OnHold
3/27/2021	3	New
3/28/2021	4	New
3/29/2021	4	OnHold
3/30/2021	4	InProcess
4/1/2021	5	New
4/2/2021	5	Shipped
4/3/2021	6	New
4/4/2021	7	New
4/5/2021	7	RX
4/6/2021	7	OnHold
4/7/2021	8	New
4/8/2021	9	New
4/9/2021	9	OnHold
4/10/2021	9	RX
4/11/2021	10	New

I need to create new table that will contain same columns but with only latest Stuatus for each order and its date. 
So the restults based on this sample would look like this:

StatusTime	OrderNumber	Status
3/24/2021	1	Shipped
3/26/2021	2	OnHold
3/27/2021	3	New
3/30/2021	4	InProcess
4/2/2021	5	Shipped
4/3/2021	6	New
4/6/2021	7	OnHold
4/7/2021	8	New
4/10/2021	9	RX
4/11/2021	10	New

How do i do that using Dax?

[AllisonKennedy]

@slav84  I don't recommend using DAX to create the new table. You could do this in Power Query instead. 

Click Transform Data in the Home tab to open Power Query. 

Then Right click on your current table > Reference.

Rename this new table.

Sort this by Status Time in Descending Order so the latest date is in first row. 

Then Right click on the OrderNumber column > Remove Duplicates.

[slav84]

Hey Alison, 

thank you for you replay. 
I do have to use DAX in this case because the original table is also used for something else and i need original table in format that it is and i need new table to create by using dax. 

[AllisonKennedy · Accepted solution]

@slav84  You explained well that you need a new table > Power Query can still keep the original table in tact. That's why my suggestion was to Right click on the Original table and REFERENCE it. This will create a new copy of the original table, and this copy will update as the original table updates. Then you can do all the transformations I suggested in Power Query where it's more efficient and won't impact report performance of filtering and usability. 

[slav84]

Thank you Alison. I folllowed your suggestion. 🙂

[AllisonKennedy]

Awesome! Sorry I wasn't clear enough in my initial post, but glad we got there in the end. 🙂