Last three from a specific year

[EpicTriffid]

Hi all,

I have a column that identifies whether a year is our most recent year for a specific KPI. Some of our KPIs have been released this year, but some others havent been updated since 2017. So, when I'm tabling this all up, I can sometimes see a nicely populated three year table, like below, and sometimes a sparsely populated table, also below:

           

I know that for the sparsely populated table there are three years of data going back, but because of how of the shifting years, I can't show it without just showing all years from 2012 to 2019. Please ignore the academic year system. My data reads 2019/20 as 2019, 2018/19 as 2018 and so on, so it all works fine. 

Is there a way to build a year measure that show all the dates from a date with a most recent year flag of "Yes" to 3 years back? So for the first table, that would be fine. It would show 2017/18, 2018/19, 2019/20. On the second one, though, it would now only show 2015/16, 2016/17, 2017/18. Voila! Fully populated tables!

I'm thinking the code could look something like:

Datesinperiod(Yeardate,"MostRecentYear"="Y", -3,Year)

I know that doesn't work, but that's the kind of logic Im looking for?

Is this even possible?

[Anonymous]

Hi @EpicTriffid ,

Maybe you can take a look at the following blog about using date function to manually defined filter condition and filter range to achieve rolling calculation.

Time Intelligence "The Hard Way" (TITHW)  

In addition, are there any specific filters that existed in your calculation formula? (e.g all, allexpect...) They can also affect the calculation results.

Regards,

Xiaoxin Sheng

[EpicTriffid]

Hi @Anonymous 

Thanks for the reply. I'm not entirely sure how to apply the blog post to my report. It seems to be more adding up totals rathet than filtering years shown. 

Thee the only functions in the data are switches to change the format.

[Anonymous]

HI @EpicTriffid ,

AFAIK, power bi visual will auto-hide blank/empty records, but it not work on your side. Can you please share some dummy data with same data structure for test? 

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng