Forum Discussion
Post PatronDynamically adjusting Graph's Legend to compare past two years
Good Afternoon,
I have a graph as this:
Currently, I am using the Filter pane to manually restrict the data to 2024 & 2025 only. The Y-Axis is essentially using a measure for Total Meetings.
Now, I need to have the graph to be updated so only 2025 & 2026 are showing. Plainly, I'd prefer not to have to manually adjust this every year.
Kindly advise on the best approach to handle this matter.
Add a column onto your date table, you could add a calculated column in DAX like
Current or Previous Year = 'Date'[Year] = YEAR ( TODAY () ) || 'Date'[Year] = YEAR ( TODAY () ) - 1or could do the same thing in Power Query or SQL.
Use that column as a filter on the chart visual.
Create a calculated column in your Date table for relative year label
Is Rolling 2 Years = IF ( 'Date'[Year] >= YEAR ( TODAY () ) - 1 && 'Date'[Year] <= YEAR ( TODAY () ), 1, 0 )Then in the visual, add Is Rolling 2 Years to Filters on this visual and set it to is 1. Done. Updates automatically every year with no maintenance needed.
2 Replies
johnt75Super User
Add a column onto your date table, you could add a calculated column in DAX like
Current or Previous Year = 'Date'[Year] = YEAR ( TODAY () ) || 'Date'[Year] = YEAR ( TODAY () ) - 1or could do the same thing in Power Query or SQL.
Use that column as a filter on the chart visual.
- cengizhanarslan
Create a calculated column in your Date table for relative year label
Is Rolling 2 Years = IF ( 'Date'[Year] >= YEAR ( TODAY () ) - 1 && 'Date'[Year] <= YEAR ( TODAY () ), 1, 0 )Then in the visual, add Is Rolling 2 Years to Filters on this visual and set it to is 1. Done. Updates automatically every year with no maintenance needed.