Dynamic Year Filtering for Multiple Visuals Based on a Selected Year

[DJims86]

Hello everyone,

I’m working on a Power BI dashboard where I have three instances of the same visualization, and I want each to display data for a different year based on a single slicer selection:

• First visual → Displays the selected year.
• Second visual → Displays the previous year.
• Third visual → Displays the next year.

For example, if the user selects 2025, the visuals should show:

• First visual: 2025
• Second visual: 2024
• Third visual: 2026

If the user selects 2018, they should display:

• First visual: 2018
• Second visual: 2017
• Third visual: 2019

What I Have Tried So Far

1. Created a disconnected table (YearSelection) for the slicer, containing distinct years.
2. Created a measure to capture the selected year:
   SelectedYear = SELECTEDVALUE(YearSelection[Year])
3. Created a measure to filter each visualization dynamically:
   ShowYearFilter = VAR SelectedYr = SELECTEDVALUE(YearSelection[Year]) RETURN IF( NOT ISBLANK(SelectedYr) && 'Enrollment_Model_Individual'[Year] IN { SelectedYr - 1, SelectedYr, SelectedYr + 1 }, 1, 0 )
4. Applied the ShowYearFilter = 1 measure as a visual-level filter on each visual.

Issue I’m Facing

• The matrix visuals only show the selected year, but the previous and next years remain blank.
• I suspect it’s because SELECTEDVALUE(YearSelection[Year]) is filtering the dataset too aggressively, preventing non-selected years from appearing.

Question

How can I dynamically display the selected year, previous year, and next year across three separate visuals while keeping them responsive to the slicer?

Any suggestions or alternative approaches would be greatly appreciated! Thanks in advance.

[Ashish_Mathur]

Hi,

Here's a way to do it without a disconnected table.  Hope this helps.

[lbendlin]

Use WINDOW functions instead  (OFFSET, for your case)

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[DJims86]

Thanks for your reply and sorry for the delay in responding. I am attaching a .pbix file with sample data that illustrates my problem.

DynamicYearPBIX

[lbendlin · Accepted solution]

[DJims86]

Thank you so, so much. Huge help. I appreciate you.