Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
slav84
Helper I
Helper I

How to calculate latest status based on date?

Hello, 

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

StatusTimeOrderNumberStatus
3/22/20211New
3/23/20211InProcess
3/24/20211Shipped
3/25/20212New
3/26/20212OnHold
3/27/20213New
3/28/20214New
3/29/20214OnHold
3/30/20214InProcess
4/1/20215New
4/2/20215Shipped
4/3/20216New
4/4/20217New
4/5/20217RX
4/6/20217OnHold
4/7/20218New
4/8/20219New
4/9/20219OnHold
4/10/20219RX
4/11/202110New

 

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:

StatusTimeOrderNumberStatus
3/24/20211Shipped
3/26/20212OnHold
3/27/20213New
3/30/20214InProcess
4/2/20215Shipped
4/3/20216New
4/6/20217OnHold
4/7/20218New
4/10/20219RX
4/11/202110New

 

How do i do that using Dax?

1 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. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

5 REPLIES 5
AllisonKennedy
Super User
Super User

@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.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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. 

@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. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Thank you Alison. I folllowed your suggestion. 🙂

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.