Dynamic Visualization Filters

[Nate_BI]

I have a forecast report and want to publish it in a BI system. But, I want the Year range to start 5 years before the year which has complete records.  And I want to start the forecast line to ignore the last two years but in a filter. The reason why we wanted to use a dynamic filter instead of the usual common filters is that once the report is published, we aren't gonna have access to it. Can you give me ideas on how I can create a dynamic filter for my visualization?

[v-angzheng-msft · Accepted solution]

Hi, @Nate_BI 

Based on what I've got, what you want to achieve is to get a range of 5 years before the last full year.
For example, if it's 2022 and the last full year is 2021, what you want to get is the five years before 2021, i.e. 2015 to 2020.
When it is now April 2023 and the last full year was 2022, you want to get is 2016 to 2021.

Try to create a measure like this:

_Range = 
var _maxDate=MAXX(ALL('Table'),[Date])
var _thisYear=YEAR(_maxDate)
var _completeYear=IF(_maxDate=DATE(_thisYear,12,31),_thisYear,_thisYear-1)

var _rangeMax=_completeYear-1
var _rangemin=_rangeMax-5

var _if=IF(SELECTEDVALUE('Table'[Date])>=DATE(_rangemin,1,1)&&SELECTEDVALUE('Table'[Date])<=DATE(_rangeMax,12,31),1,0)
return _if

Then filter the measure in filter pane.

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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