Solved - How to calculate rolling 12 month total in DAX ignoring slicer filters on year

📌 The Problem Explained

In a standard Power BI report, users often apply a Year Slicer to narrow down their view. However, if you are trying to calculate a Rolling 12 Month Total, a standard year filter creates a major roadblock.

For example, if a user selects “2024” in a slicer, the filter context restricts the data to only January 2024 through December 2024. If you want to see the rolling total for February 2024, the calculation should include data from March 2023. Because the year slicer has “killed” any data before January 1st, 2024, your calculation returns an incomplete and incorrect value. This is a common frustration in Power BI workflow automation when building financial or sales dashboards.

💡 The Quick Solution

To fix this, you must use the CALCULATE function combined with DATESINPERIOD and a filter modifier like ALL or REMOVEFILTERS to explicitly tell Power BI to ignore the year selection.

Copy and paste this pattern into a new measure:

Rolling 12M Total =
CALCULATE(
    [Your base measure],
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH),
    ALL('Date'[Year])
)

🛠️ Step-by-Step Breakdown

Follow these steps to implement the measure correctly in your Power BI desktop application.

Step/Argument	Action	Description
1. Base Measure	[Total Sales]	Ensure you have a simple sum measure already created (e.g., SUM(Sales[Amount])).
2. Date Column	'Date'[Date]	You must use a continuous Date Table for Time Intelligence functions to work accurately.
3. End Date	MAX('Date'[Date])	This identifies the last date in the current context (the “current month” in your visual) to look back from.
4. Interval	-12, MONTH	Tells DAX to look back exactly 12 intervals of the type “Month.”
5. Ignore Slicer	ALL('Date'[Year])	This is the “magic” line. It forces the calculation to look at the whole calendar, ignoring whatever the user picked in the Year slicer.

How to apply this in the UI:

1. Go to the Data pane on the right.
2. Right-click your Sales table and select New measure.
3. Enter the code provided in the Quick Solution.
4. Drag this new measure into a Line Chart or Table.
5. Ensure your X-axis or Table rows use the Month or Date column from your Date table.

⚠️ Common Mistakes to Avoid

• Not using a Date Table: If you use the date column directly from your “Fact” (Sales) table, the DATESINPERIOD function may fail if there are gaps in dates (e.g., no sales on Sundays). Always use a dedicated Calendar/Date Table.
• Using ALL on the whole table: If you write ALL('Date') instead of ALL('Date'[Year]), the measure will ignore all date filters, including the specific month you are looking at in a chart. This will result in the same total being repeated for every single row.
• Circular Dependency: Ensure your Year slicer is pulling from the exact same column referenced in the ALL() part of your formula. If your slicer uses Date[Year] but your formula says ALL(Date[CalendarYear]), the filter will still stay active and break the calculation.

By mastering this Power BI troubleshooting technique, you can create dynamic reports that remain accurate regardless of how users interact with slicers.