Guides

Rolling 12-Month Average in Power BI DAX

By Ravindra Bagale, Power BI & Data Analytics trainer · LinkedIn · Published: 30 September 2026 · Last updated: 30 September 2026

A rolling 12-month average looks back twelve months from the date in context and tells you the typical month in that window — it is not year-to-date, and it is not an average of every single day unless you asked for days.

DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH )

Why this pattern, why a filter, what comes out

One month on the axis is not a year. The formula has to fetch more rows, add them, then turn that into one average.

Why DATESINPERIOD:

1. The visual is sitting on March 2026. You still want April 2025 through March 2026.
2. DATESINPERIOD builds that list of dates. The minus 12 and the word MONTH are the length and the step.
3. CALCULATE uses that list as a filter, so [Total Sales] sees those months' rows, not only March.

Why this is a filter even when you do not type FILTER:

1. A time-intelligence table of dates is a filter argument. Rows outside the window stay out.
2. The hand-written cousin is FILTER on the Date table: keep dates that are on or before the last visible date and inside the twelve months. Same story, more typing.
3. Use the FILTER function when the rule is awkward for DATESINPERIOD. Use DATESINPERIOD for a plain trailing window.
4. You still need a marked Date table. The fact table's own dates have gaps, and the window would skip silent days.

What happens for one point on the line:

1. Rows in at the start: only March, because that is the axis point.
2. The window filter replaces that with twelve months of dates.
3. SUM walks every sales row in those months and returns one number, the total.
4. DIVIDE by 12 returns one number, the average month. A quiet month counts as zero in that version.
5. The next axis point (April) runs the same steps on a window that moved forward one month.

If you use AVERAGEX on the month values instead, blank months drop out of the average. That is a different sentence. Pick one and write it on the chart title.

Real example: the shop takings and the phone bill

FreshBasket Mumbai took these sales in the last few months. Pune is the other shop. The owner also stares at the phone bill, because that one number is easy to feel.

Why a rolling average, not YTD: in March the owner asks "what is a normal month?", not "what have we added up since January?"

What happens on the March point:

1. The axis is only March. Those rows would go in if you did nothing else.
2. DATESINPERIOD with minus 12 months is the filter that pulls the earlier months back in. With only three months of history you still divide carefully, but the idea is the same: a window of months, not one dot.
3. Sales rows in the window are added to one total, then divided by 12 if a quiet month should count as zero. Number out: one average, for the card titled "average month".
4. The phone bill makes the same shape obvious. Jan 499, Feb 499, Mar 699. A 3-month average of the bill is about 566. YTD of the bill is 1697, the sum, and it would restart next January. Do not put 1697 on a card labelled "average".

Why you might type FILTER: only when you are building that date list yourself ("dates on or before March, and not older than 12 months"). DATESINPERIOD is that filter already packed for you. Either way, rows from outside the window stay out, and one number comes out.

Pune's bill is 399 every month. If the Mumbai card does not move when you slice to Pune, you are not filtering the city. The window filter and the city filter must both be true.

What do I need before this guide?

• Year-to-date so you can feel the difference.
• DIVIDE.
• Turn off Auto date/time if a hidden date table is still on.
• Course: Time intelligence.

Before and after (look at the tables first)

The window

DATESINPERIOD ( dates, start_date, number_of_intervals, interval ) returns a continuous set of dates. A negative number walks backward.

Sales R12 Avg =
VAR LastDate =
    MAX ( 'Date'[Date] )
VAR Period =
    DATESINPERIOD ( 'Date'[Date], LastDate, -12, MONTH )
VAR SalesInWindow =
    CALCULATE ( [Total Sales], Period )
RETURN
    DIVIDE ( SalesInWindow, 12 )

Read it vertically:

1. LastDate is the latest date still visible (the month on the axis, or the day, depending on the visual).
2. Period is the twelve months ending there.
3. CALCULATE moves [Total Sales] onto that period.
4. Dividing by 12 is a choice: empty months count in the denominator. Say that in the title: “average month, quiet months as zero”.

Grain: months, not days

1. If you average every day in the window, a busy Saturday dominates the story. That is a daily average.
2. This guide’s question is the average month.
3. Put 'Date'[Year Month] (or a FORMAT column you add on the Date table) on the axis.
4. A second version averages only months that have sales. AVERAGEX skips blanks:

Sales R12 Avg Sold Months =
VAR LastDate =
    MAX ( 'Date'[Date] )
VAR Period =
    DATESINPERIOD ( 'Date'[Date], LastDate, -12, MONTH )
RETURN
    CALCULATE (
        AVERAGEX ( VALUES ( 'Date'[Year Month] ), [Total Sales] ),
        Period
    )

5. Use this only when “ignore months with no sales” is the sentence. Otherwise the divide-by-12 version is the honest average of the trailing year.
6. If you do not have Year Month yet, add a column on the Date table: Year Month = FORMAT ( 'Date'[Date], "YYYY-MM" ).

Not year-to-date

1. TOTALYTD starts at the beginning of the year and stops at the current date. January resets it.
2. A rolling 12 in March still includes last April through this March.
3. Put both measures on one line chart once, in practice, and watch January. YTD drops. Rolling usually does not.
4. Name them Sales YTD and Sales R12 Avg. Vague names create meetings.

What must already be true

1. The Date table has every day, no gaps.
2. It is marked as a date table.
3. Sales joins it on a date (not a leftover date-time that breaks the relationship).
4. The visual’s axis uses that Date table, not a stray date column on Sales.

Ghabru naka — a blank rolling average is usually the Date table, not the minus sign.

Mistakes and calm fixes