DAX create a column for max date by Order Id

[PAPalmer]

Hello, 

I am trying to get the Max date for the order id . Here is what I have as my table:

Order Id	Date	Status
173	3/14/2022	Submitted
173	3/19/2022	Level 2
173	3/20/2022	Level 3
173	3/22/2022	Level 4
173	3/25/2022	Level 5
549	6/27/2022	Submitted
549		Level 2
549	7/06/2022	Level 3
549	07/06/2022	Level 4
549	07/08/2022	Level 5

There are some Status which may not have a date. The desired output is to have a separate column to display the max date for each order id , which should be the Level 5 dates.
I have tried the below two approaches.

Latest Dt = CALCULATE(MAX('Orders'[Date]),ALLEXCEPT('Orders','Orders[Order Id]))

Latest Dt = CALCULATE(MAX('Orders'[Date]),
                      FILTER('Orders','Orders[Order Id]=EARLIER('Orders'[Order Id]))

 Both these are displaying the date for the Submitted status, which is the minimum date. 

Any help will be appreciated. 

Thank you,

Petra

[Anonymous · Accepted solution]

Hi @PAPalmer 

Plesse try the following dax:

Measure = 
VAR _max_date = MAX('Table'[ Date ])
RETURN CALCULATE(MAX('Table'[Status]),FILTER('Table','Table'[ Date ] = _max_date))

This is the result you want:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PAPalmer]

Hi  v-jialongy-msft, 

Thank you so much, it works well where all the dates are in the same year. 
For dates that  shift to another year  eg : 

Order Id	Date	Status
457	11/2/2022	Submitted
457	12/1/2022	Level 1
457	12/15/2022	Level 2
457	12/22/2022	Level 3
457	12/30/2022	Level 4
457	1/10/2023	Level 5

It is picking the min value , which is 11/2/2022. Could you please help me with it . Appreciate your help

Thank you,

Petra.