Rolling 12 months in DAX: the two-calendar solution

TL;DR

• Dynamic rolling 12 months requires two calendars: one for the slicer, one for the X-axis
• The relationship between calendars must be inactive (activate with USERELATIONSHIP)
• Complex pattern: ask the client if they really need it before implementing
• Sometimes a simple range filter is enough

---

The requirement

Sounds simple: a line chart with the last 12 months, and a slicer to choose the end month.

You select December 2024, and the chart shows January 2024 - December 2024. You select March 2025, it shows April 2024 - March 2025.

Easy, right?

Nope.

The problem

If you use a single calendar, you have a conflict of interest:

1. The slicer filters by month (December 2024)
2. The chart’s X-axis needs to show 12 different months

With a single calendar, when you filter by December, the chart only has one point. The filter affects everything. It’s like trying to use the same variable for two different things: it doesn’t work.

The solution: Two calendars

The architecture you need is:

• Calendar: Connected to the fact table, feeds the slicer
• Calendar_Display: Feeds the chart’s X-axis, NOT connected to the fact table

The key is creating an INACTIVE relationship between both calendars. You wake it up only when you need it, with USERELATIONSHIP.

The DAX pattern

Rolling 12 Sales =
VAR SelectedDate =
    CALCULATE(
        MAX(Calendar[Date]),
        ALLEXCEPT(Calendar, Calendar[Year], Calendar[Month])
    )
VAR Last12Months =
    DATESINPERIOD(
        Calendar_Display[Date],
        SelectedDate,
        -12,
        MONTH
    )
VAR CurrentAxisDate = SELECTEDVALUE(Calendar_Display[Date])
VAR IsInRange =
    CurrentAxisDate >= MINX(Last12Months, [Date]) &&
    CurrentAxisDate <= MAXX(Last12Months, [Date])
RETURN
IF(
    IsInRange,
    CALCULATE(
        [Sales],
        REMOVEFILTERS(Calendar),
        KEEPFILTERS(Last12Months),
        USERELATIONSHIP(Calendar[Date], Calendar_Display[Date])
    ),
    BLANK()
)

What each part does

1. SelectedDate: Captures what the user chose in the slicer
2. Last12Months: Calculates the 12-month range backwards from that date
3. REMOVEFILTERS: Clears the slicer filter (otherwise you’d only have 1 month in the chart)
4. KEEPFILTERS: Maintains the 12-month range we just calculated
5. USERELATIONSHIP: Activates the inactive relationship between calendars
6. IsInRange: Prevents showing data outside the range (otherwise you get ghost points)

Why the relationship must be inactive

If you activate it permanently, you create ambiguity in the model. Power BI sees two possible paths between the calendar and the facts, and doesn’t know which to follow.

That’s why you leave it inactive by default and only activate it in the specific measures that need it. It’s like a switch: only on when necessary.

Before implementing this

This pattern is complex and hard to maintain. Before going down this rabbit hole, ask the client if they really need a dynamic rolling or if a simple date range filter would do.

Sometimes the answer is “oh, yeah, a date filter works fine.” And you just saved yourself three hours of debugging.

The USERELATIONSHIP function is key to making this pattern work—if you’re not familiar with inactive relationships, read that post first. And if you’re new to DAX in general, start with my beginner’s guide before tackling these advanced patterns.