Changing filter context dynamically when cross-filtering visualisations

[PowerWhy]

Hello,

I have the pbix file below which shows actuals and forecast accross 3 years, the slicer is used to select the year. I have actuals up to period 11 of the 2nd year.

When the user selects a year with at least 1 month but less than 12 months of actuals, I have been asked to show the last 12 months. Otherwise the graph should show the 12 months of the selected fiscal year.

https://www.dropbox.com/s/oxnni8837m3n6b8/Mockup%20-%20slicer%20different%20treatments%20-%202022%20...

Currently 2022 shows:


But it should show:


And 2021 should stay unchanged as:

And 2023 should stay unchanged as (it shows 12 months of forecast because there are no actuals):


I guess I'm asking if it's possible to change the filter context of the axis labels, but I'm not sure it is?? (I was wondering if using bookmarks instead of a slicer, and filtering the visualisations by a measure might work, but can't quite see how to do it)

Many thanks for any pointers,

PW

[PowerWhy]

Hi @amitchandak sorry I'm not following - are you sure that changes the axes of the graphs? (not just changes the values of the data points?)

[amitchandak]

@PowerWhy , You need an independent date table

example

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max1 = maxx(allselected(Date1),Date1[Date])

var _max = if(Year(_max1) = Year(Today()) , eomonth(_max1 ,-1) ,_max1 )
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI