Override page-level date filter for visual

[mmcanelly]

I have a problem that seems fairly simple that I've spent half the day trying (unsuccessfully) to solve. I have a line chart on a report where I want to always show the past 5 years of history, regardless of what date filters are applied to the report. Here's an example of what the chart looks like:

If I filter the report by a single year with the Year filter I have on the page, it will only show 1 point on this line chart. The basic measure I've set up to try to solve this is as follows:

CALCULATE (
    [Other Measure],
    Dates[YearsAgo] < 5
)

This on its own doesn't ignore the date filter that's set on the report, so I've tried different DAX solutions by using ALL(), REMOVEFILTERS(), and ALLEXCEPT() in different ways within the CALCULATE() expression. The results I end up with in all these cases are either (1) The measure calculates a single value for the entire dataset because it's ignoring the year, so I end up with a straight line on the line chart, or (2) The Year filter on the page will still filter down the chart to a single point.

It looks like there's a common solution using a slicer and the Edit Interactions function, but I'm not currently using slicers on this report and I would rather not, since it would be a little confusing for the user with some filters appearing in slicers on the report and some appearing in the filter pane.

This seems like a super simple problem, but I can't figure out what I'm doing wrong here. Any advice to keep my chart consistently showing 5 years regardless of the Year filter set on the report?

[v-junyant-msft · Accepted solution]

Hi @mmcanelly ,

The test data sheet name is 'Table':

You can use the following DAX to create a New Table:

Last5YearsData = 
CALCULATETABLE(
    'Table',
    FILTER(
        ALL('Table'[Year]),
        'Table'[Year] >= MAX('Table'[Year])-4)
)

The table you created 'Last5YearsData' will only contain data for the last 5 years.
Then use the table you just created, 'Last5YearsData', to create a line chart. And the final output is shown in the following figure:

When I apply the filter, nothing changes:

When I applied slicer, it didn't change either:

Note that when the year comes to the next, you only need to refresh it and you don't need to do anything else to update the line chart.

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.