topic Dynamic Rolling 12 Months Sales Based on Selected Month in DAX Commands and Tips

Dynamic Rolling 12 Months Sales Based on Selected Month

Chanakya_vcr — Mon, 17 Nov 2025 13:57:01 GMT

How to Create a Rolling 12-Month Sales Measure Based on Slicer Selection?

Hello everyone,

I’m looking for help in creating a DAX measure that calculates Rolling 12 Months (Running 12M) Sales based on the user’s selection in a Date slicer.

Requirement:
If the user selects Aug-2025 in the slicer, the line chart should automatically display sales from:
Sep-2024 to Aug-2025 (the last 12 full months based on the selected month).

Model details:

I have a proper Date Table marked as a date table.
The Sales table is connected to the Date table through the Date column.

I need a DAX measure that dynamically returns sales for the last 12 months according to the month selected in the slicer.

Any guidance or sample DAX would be greatly appreciated.
Thanks in advance!

Re: Dynamic Rolling 12 Months Sales Based on Selected Month

Abhilash_P — Mon, 17 Nov 2025 14:10:21 GMT

Hi Chanakya_vcr ,

Below DAX function will help in calculating last 12 months running total based on slicer selection

Rolling 12M Sales := VAR MaxVisibleDate = MAX('Date'[Date]) RETURN CALCULATE( [Total Sales], DATESINPERIOD( 'Date'[Date], MaxVisibleDate, -12, MONTH ) )

MaxVisibleDate captures the month selected in the slicer.
DATESINPERIOD goes 12 months back from the last visible date.

Re: Dynamic Rolling 12 Months Sales Based on Selected Month

Jihwan_Kim — Mon, 17 Nov 2025 18:17:23 GMT

Hi,

I tried to create a sample pbix file like below, and one of ways to achieve this is to have disconnected slicer table.

Please check the below picture and the attached pbix file.

WINDOW function (DAX) - DAX | Microsoft Learn

KEEPFILTERS function (DAX) - DAX | Microsoft Learn

Sales 12 months rolling: = CALCULATE ( SUM ( sales[sales] ), KEEPFILTERS ( WINDOW ( 1, ABS, 12, ABS, FILTER ( ALL ( 'calendar'[Year-Month], 'calendar'[Year-Month sort] ), 'calendar'[Year-Month sort] <= MAX ( calendar_slicer[Year-Month sort] ) ), ORDERBY ( 'calendar'[Year-Month sort], DESC ) ) ) )

Re: Dynamic Rolling 12 Months Sales Based on Selected Month

Chanakya_vcr — Tue, 18 Nov 2025 05:42:27 GMT

Hi Jihwan_Kim ,
Thanks for sharing this!