Dynamically adjusting Graph's Legend to compare past two years

[F_Reh]

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.

[johnt75 · Accepted solution]

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 () ) - 1

or could do the same thing in Power Query or SQL.

Use that column as a filter on the chart visual.

[cengizhanarslan · Accepted solution]

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.

[johnt75 · Accepted solution]

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 () ) - 1

or could do the same thing in Power Query or SQL.

Use that column as a filter on the chart visual.