MOVINGAVERAGE
In a visual calculation, the average of a column over a window of rows along an axis, up to the current one.
A visual calculation works on the table behind a visual: a row for each group on the visual's axes and one for each of their totals. To try one in the OAX view, give a table that shape with DEFINE TABLE ... WITH VISUAL SHAPE: its axes, their groups, the TRUE/FALSE column that marks each group's totals and the order, and with DENSIFY a name for the column that marks rows the shape fills in. Then write the calculation as a COLUMN of that table, as Power BI's own queries do. The examples below are queries of that kind, small enough to check by hand.
Group: Window and visual calculations. Compatible with DAX.
Syntax
MOVINGAVERAGE ( <column>, <windowSize> [, <includeCurrent>] [, <axis>] [, <orderBy>] [, <blanks>] [, <reset>] )| Parameter | Type | Description |
|---|---|---|
column | Column | A field of the visual, such as [Liters]. An expression is not allowed here: make it a visual calculation of its own and name that. |
windowSize | Whole number | How many rows the window holds; 1 or more. |
includeCurrent | TRUE/FALSE optional | TRUE (when left out): the window ends with the current row. FALSE: it holds the rows before it. |
axis | Axis optional | Which way to go: ROWS or COLUMNS, or both, as ROWS COLUMNS, which goes down the rows first and then across the columns; ROWPAGES for small multiples. Left out, the visual's first axis. |
orderBy | ORDERBY optional | ORDERBY ( ... ) to take the rows in another order than the axis has. |
blanks | Keyword optional | With an ORDERBY: where blank values go, DEFAULT, FIRST or LAST. |
reset | Reset optional | Where it starts again: NONE (when left out) never; HIGHESTPARENT (or 1, or the axis's top column) at each value of the top group; LOWESTPARENT (or -1) at each row's own parent; another whole number, or a column of the axis, at that level. |
Returns
Number. The mean of the column over the window.
Remarks
- Near the start there are fewer rows than the window holds: the mean is of those there are. With includeCurrent FALSE, the first row has none and is BLANK.
- Blank values are left out of the mean, as
AVERAGEleaves them out. - The window stays inside a partition: with a reset, it does not reach back into the previous year.
- Each level of the visual is walked on its own: the quarters' rows among the quarters, the years' subtotals among the years, the grand total by itself.
- It works only in a visual calculation; in a measure, a calculated column or a plain query it is an error.
Examples
Two-month moving average
DEFINE
TABLE Visual =
DATATABLE (
"Month", INTEGER, "Liters", INTEGER,
{ { 1, 100 }, { 2, 150 }, { 3, 120 }, { 4, 90 } }
)
WITH VISUAL SHAPE
AXIS ROWS GROUP [Month] ORDER BY [Month]
DENSIFY "Added"
COLUMN Visual[Two months] = MOVINGAVERAGE ( [Liters], 2 )
COLUMN Visual[Two before] = MOVINGAVERAGE ( [Liters], 2, FALSE )
COLUMN Visual[Three months] = MOVINGAVERAGE ( [Liters], 3 )
EVALUATE
SELECTCOLUMNS (
Visual,
"Month", [Month], "Liters", [Liters],
"Two months", [Two months],
"Two before", [Two before],
"Three months", [Three months]
)
ORDER BY [Month]| Month | Liters | Two months | Two before | Three months |
|---|---|---|---|---|
| 1 | 100 | 100 | (blank) | 100 |
| 2 | 150 | 125 | 100 | 125 |
| 3 | 120 | 135 | 125 | 123.333333333333 |
| 4 | 90 | 105 | 135 | 120 |
A month without a value
Month 2 has no liters: the windows that hold it average the other month only.
DEFINE
TABLE Visual =
DATATABLE (
"Month", INTEGER, "Liters", INTEGER,
{ { 1, 100 }, { 2, BLANK () }, { 3, 120 }, { 4, 90 } }
)
WITH VISUAL SHAPE
AXIS ROWS GROUP [Month] ORDER BY [Month]
DENSIFY "Added"
COLUMN Visual[Two months] = MOVINGAVERAGE ( [Liters], 2 )
EVALUATE
SELECTCOLUMNS (
Visual,
"Month", [Month], "Liters", [Liters],
"Two months", [Two months]
)
ORDER BY [Month]| Month | Liters | Two months |
|---|---|---|
| 1 | 100 | 100 |
| 2 | (blank) | 100 |
| 3 | 120 | 120 |
| 4 | 90 | 105 |
Restarting each year
DEFINE
TABLE Visual =
DATATABLE (
"Year", INTEGER, "Quarter", INTEGER, "Liters", INTEGER, "All years", BOOLEAN, "All quarters", BOOLEAN,
{
{ 2025, 1, 100, FALSE, FALSE }, { 2025, 2, 120, FALSE, FALSE }, { 2025, BLANK (), 220, FALSE, TRUE },
{ 2026, 1, 130, FALSE, FALSE }, { 2026, 2, 150, FALSE, FALSE }, { 2026, BLANK (), 280, FALSE, TRUE },
{ BLANK (), BLANK (), 500, TRUE, TRUE }
}
)
WITH VISUAL SHAPE
AXIS ROWS
GROUP [Year] TOTAL [All years]
GROUP [Quarter] TOTAL [All quarters]
ORDER BY [Year], [Quarter]
DENSIFY "Added"
COLUMN Visual[Within the year] = MOVINGAVERAGE ( [Liters], 2, HIGHESTPARENT )
EVALUATE
SELECTCOLUMNS (
Visual,
"Year", [Year], "Quarter", [Quarter], "Liters", [Liters],
"Within the year", [Within the year]
)
ORDER BY ISBLANK ( [Year] ), [Year], ISBLANK ( [Quarter] ), [Quarter]| Year | Quarter | Liters | Within the year |
|---|---|---|---|
| 2025 | 1 | 100 | 100 |
| 2025 | 2 | 120 | 110 |
| 2025 | (blank) | 220 | 220 |
| 2026 | 1 | 130 | 130 |
| 2026 | 2 | 150 | 140 |
| 2026 | (blank) | 280 | 280 |
| (blank) | (blank) | 500 | 500 |