Forum Discussion

PowerWhy's avatar
PowerWhy
Helper IV
3 years ago

Changing filter context dynamically when cross-filtering visualisations

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%2009%2002%201158am.pbix?dl=0

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

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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

     

  • PowerWhy's avatar
    PowerWhy
    Helper IV
    3 years ago

    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?)