Show last updated category for each ID

[prasadhebbar315]

Hello,

I have 3 columns (ID, Status and Last_Updated_Date). ID can repeat and the status can change from Low->Moderate->High->Critical. I would like to capture Max_Date for each ID based on max(Last_Updated_Date) and Latest_Status is Status from max(Last_Updated_Date). Here is an example from excel file. Can someone help achive this in PBI please?

ID	Status	Last_Updated_Date	Max_Date	Latest_Status
100	High	2/14/2023	2/14/2023	High
100	High	2/13/2023	2/14/2023	High
100	Low	2/12/2023	2/14/2023	High
200	Critical	2/12/2023	2/12/2023	Critical
300	Moderate	2/13/2023	2/13/2023	Moderate
300	Low	2/12/2023	2/13/2023	Moderate

[Ahmedx · Accepted solution]

Max_Date = 
CALCULATE (
    MAX ( 'Table'[Last_Updated_Date] ),
    ALLEXCEPT ( 'Table', 'Table'[ID] )
)
-----
Latest_Status = 
CALCULATE (
    MAX ( [Status] ),
    ALL ( 'Table' ),
    'Table'[ID] = EARLIER ( 'Table'[ID] )
        && 'Table'[Last_Updated_Date] = 'Table'[Max_Date]
)

https://dropmefiles.com/RrfxE

[miTutorials · Accepted solution]

I have explained in this tutorial please do check this out.

Get another column value based on the Max Date or Max Value in PowerBI 

[prasadhebbar315]

Thanks @miTutorials 

[Ahmedx · Accepted solution]

Max_Date = 
CALCULATE (
    MAX ( 'Table'[Last_Updated_Date] ),
    ALLEXCEPT ( 'Table', 'Table'[ID] )
)
-----
Latest_Status = 
CALCULATE (
    MAX ( [Status] ),
    ALL ( 'Table' ),
    'Table'[ID] = EARLIER ( 'Table'[ID] )
        && 'Table'[Last_Updated_Date] = 'Table'[Max_Date]
)

https://dropmefiles.com/RrfxE

[prasadhebbar315]

@Ahmedx Fantastic. Your solution works 🙂