Filter date by most recent non-blank year backwards

[EpicTriffid]

Hi all,

So we a situations where our most recent KPI scores are released on different years. One KPI might have its most recent score in 2019, while another might have its most recent score in 2017. I need to show 3 years of data, much like the picture below:

Here, the most recent figures has been released in 2019, so we get three year rolling backwards. We can call this KPI X.

In the picture below, KPI Y's most recent figure is from 2018. We have 2016 data, but the year column is stuck on 2017-2019:

Is it possible to make it so that the year filter is dynamic, and adjusts to show the most year of data on the right, followed by the preceding two years of data?

Cheers!

[v-chuncz-msft]

@EpicTriffid 

You may refer to the post below to add a calculated column.

https://community.powerbi.com/t5/Desktop/Calculate-Sales-first-year-second-year-etc/m-p/451157#M2089...

[EpicTriffid]

Hi @v-chuncz-msft 

I dont quite understand this. Could you please explain in a bit more detail?

[EpicTriffid]

Im sorry, I dont quite understand how that formula works? Could you please explain what it's doing?