Downloads
DAXRanking and windowsAdvanced

WINDOW

Returns a block of rows from an ordered list, with positions counted from the start of the list or from the current row.

Syntax
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

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

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.

Related functions

All DAX functions · View as Markdown · DAX fundamentals