Rolling 3-month average

Average monthly sales over the current month and the two months before it.

Rolling averages · Intermediate · Not yet reviewed

Sales 3-Month Rolling Avg =
VAR LastDate = MAX('Date'[Date])
VAR WindowSales =
    CALCULATE(
        [Total Sales],
        DATESINPERIOD('Date'[Date], LastDate, -3, MONTH)
    )
RETURN
    DIVIDE(WindowSales, 3)

How it works

DATESINPERIOD returns all dates in the three months that end on the last date of the current period. Summing sales over that window and dividing by 3 gives the average month.

Place the measure on a visual with months on the axis. Each point then smooths the three most recent months.

What your model needs

- A date table named 'Date' with one row per day and no gaps, marked as the date table in Power BI (Table tools > Mark as date table). - A one-to-many relationship from 'Date'[Date] to the date column of the Sales table (for example Sales[OrderDate]). - Total Sales = SUM(Sales[Amount]) (called [Total Sales]).

Small example

Input data

Month | Total Sales
Jan | 90
Feb | 120
Mar | 150
Apr | 210

Expected result

Month | 3-Month Rolling Avg
Mar | 120
Apr | 160

Common mistakes and limitations

- For the first two months of data the window is incomplete, so dividing by 3 understates the average. Hide those points or divide by the number of months that exist. - Dividing by 3 assumes a month-level axis. For daily axes use the 7-day version.

Tags: rolling average, moving average, DATESINPERIOD