How to get the latest status of my project per year

[crolland]

Hello,

I have one table (EPIC_History) with the monthly updates of my projects per fiscal year. 

update on, project name, update, status, fiscal year

April 2021, Project A, update blabla, on going, FY 2021-2022

May 2021, Project A, another update, on going, FY 2021-2022

June 2021, Project A, another update 2, on going, FY 2021-2022

April 2022, Project A, another update, on going, FY 2022-2023

July 2022, Project A, another update, at risk, FY 2022-2023

September 2022, Project A, another update, blocked, FY 2022-2023

April 2023, Project A, another update, at risk, FY 2023-2024

I am looking for DAX request in order to get the latest status for each fiscal year : 

Project A, on going, FY 2021-2022

Project A, blocked, FY 2022-2023

Project A, at risk, FY 2023-2024

I tried : summarize, filter, max... no luck. Need your help.

[crolland]

Thank you very much ! It worked perfectly. 

[v-yalanwu-msft · Accepted solution]

Hi, @crolland ;

You could create a measure by dax.

latest status = 
var _last=CALCULATE(MAX('Table'[update on]),ALLEXCEPT('Table','Table'[fiscal year]))
return CALCULATE(MAX('Table'[status]),FILTER('Table',[update on]=_last))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.