WINDOW
Returns a block of rows from an ordered list, with positions counted from the start of the list or from the current row.
WINDOW(<from>, <from_type>, <to>, <to_type> [, <relation>] [, <orderBy>] [, <blanks>])Returns: A table.
What it does
WINDOW returns a block of rows from an ordered list, for example from the first row up to the current row. Pair it with SUMX to build a running total, or with AVERAGEX for a moving average. It belongs to the newer window functions. Check that your version of Power BI or Excel supports them before you use them in a shared report.
Syntax and arguments
- from and to: the positions where the block starts and ends, as numbers.
- from_type and to_type:
ABScounts from the start of the list, so 1 is the first row.RELcounts from the current row, so 0 is the current row and -2 is two rows before. - relation (optional): the rows to consider, usually
ALLSELECTEDof a column that the visual uses. - orderBy (optional):
ORDERBYof the column that puts the rows in sequence. - blanks (optional): how blank rows are treated. See the Microsoft reference for the options.
Example
A running total of sales from the first day shown up to the current day, in a daily chart.
Running Sales =
SUMX (
WINDOW (
1, ABS,
0, REL,
ALLSELECTED ( 'Date'[Date] ),
ORDERBY ( 'Date'[Date] )
),
[Total Sales]
)
For each day, the window runs from the first day in the selection to the current day, and the sales in that block are added up. The total grows across the chart.
Example: a second use
A three-period moving average, using the current row and the two rows before it.
Three Period Average =
AVERAGEX (
WINDOW (
-2, REL,
0, REL,
ALLSELECTED ( 'Date'[Date] ),
ORDERBY ( 'Date'[Date] )
),
[Total Sales]
)
Each point on the chart shows the average of the current period and the two before it. This smooths out a noisy monthly series.
Things to watch
- Confirm that your version supports the window functions.
- The relation and the sort order must match the visual's axis. If they do not, the windows are wrong.
- Test with a small table first. Window results follow the visual, so a totals row can behave differently from the rows above it.
- Large windows on big tables can be slow.
- For one row back,
OFFSETis simpler. For calendar periods, time intelligence functions are usually clearer.
In business terms
Running totals of cash or collections, cumulative views of spending against a budget, and moving averages of returns or collections that smooth out monthly noise before a review.