Rolling Averages Kit

Back

Rolling Averages Kit

Moving Averages & Running Total via Calculation Group

DAX

Overview

Features

Prompts

Troubleshooting

Key Features

Your line chart shows monthly revenue, but stakeholders want to see the trend smoothed over 3 or 6 months alongside the raw actuals. Writing a separate DAX measure for each KPI × each window size means dozens of redundant measures to maintain. The correct pattern uses a calculation group with SELECTEDMEASURE() so one set of items applies to every measure in the model. But the DAX is easy to get wrong: WINDOW breaks in calculation group contexts, partial windows at the start of a series need explicit month-count guards or they silently return averages with wrong denominators, and TMDL syntax errors (spaces instead of tabs, stray lineageTag properties) silently corrupt the file. This skill generates the correct CALCULATE + DATESBETWEEN + FIRSTNONBLANK pattern, handles both merge (into an existing Time Intelligence group) and create (new group) modes, and writes valid TMDL in one command.

How it works

Type /rolling-averages-kit, point it to your PBIP project, and the skill builds the calculation group:

Scans the model to detect the date table, date column (dateTime), any existing calculation groups, and whether the calendar extends past the last data date
Asks one question: does your date table extend to Dec 31 of the max year, or does it stop where your data stops? (The scan infers the answer and pre-selects the recommended option — you just confirm)
If one calculation group exists, merges the 5 new items into it; if none exists, creates a new Time Window group; if multiple exist, asks which group to target
Generates 5 calculation items using the CALCULATE + DATESBETWEEN + FIRSTNONBLANK pattern, with the correct anchor mode based on your calendar type
Writes valid TMDL (tabs, UTF-8 without BOM, no lineageTag on calc items) and patches model.tmdl if a new group is created

What you get

Actual — pass-through, returns SELECTEDMEASURE() unmodified
Rolling Avg 3M — 3-month moving average; returns BLANK for the first 2 months of the series (insufficient history)
Rolling Avg 6M — 6-month moving average; returns BLANK for the first 5 months of the series
Rolling Avg 12M — 12-month moving average; returns BLANK for the first 11 months of the series
Running Total — cumulative from the first visible date in context, respects page filters and slicers