Show SelectedYear and Future Years data in Bar Chart

DAX DAX Calculations
1

Hi,
My Report has date_dim and benefits_fact table which has measure benefits along with Year & Month columns

I have three pages report with different views(hard coded filters of other dimensional columns). I have Year slicer from date dimension on all three pages and sync is in tact when one slicer is selected in any page all of them pages filters by selected year. In one of the pages, I have a bar chart which needs to displayed with selected year and plus 3 future years … For example if year 2020 is selected then I need to show 2020,2021,2022,2023 and if year 2021 is selected I need to show 2021,2022,2023,2024.

Impact of SlicerSelection on Visual

SlicerSelectionYear
SlicerSelectionYear1303×672 61.4 KB

Desired Result with Slicer Selection

SlicerSelectionYear+3
SlicerSelectionYear+31301×671 67.3 KB

How can I achieve this via DAX . Please help or guide me with an approach
PS : I have gone through disconnected table method however in my scenario I want to keep all the slicer across all pages in Sync so it does not serve the need.
Thanks
Jayden

2

Maybe something like below:

Measure =
VAR SelectedYear =
    SELECTEDVALUE ( DateTable[Year] )
VAR FutureYears =
    FILTER ( ALL ( DateTable ), DateTable[Year] >= SelectedYear )
VAR Result =
    CALCULATE ( [Measure], FutureYears )
RETURN
    Result

I have use whole of the date table inside ALL only so that you can add more conditions later but if you are content with just years and won’t need to add more conditions then you could just use

=
VAR SelectedYear =
    SELECTEDVALUE ( DateTable[Year] )
VAR Result =
    CALCULATE ( [Measure], DateTable[Year] > SelectedYear )
RETURN
    Result
1 Like
3

Awesome you were spot on and incredibly picked it up

4

Thanks! I am glad I could help :slight_smile: