WINDOW

Applies to: Calculated column Calculated table Measure Visual calculation

Returns multiple rows that are positioned within the given interval.

Syntax

WINDOW ( from[, from_type], to[, to_type][, <relation> or <axis>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>][, <reset>] )

Parameters

Return value

All rows from the window.

Remarks

Except for columns added by DAX table functions, each column in relation (when matchBy isn't present), or each column in matchBy and partitionBy (when matchBy is present), must have a corresponding outer value to help define the current row on which to operate. If from_type and to_type both have value ABS, the following applies only to the partitionBy columns:

• If exactly one corresponding outer column exists, its value is used.
• If no corresponding outer column exists:
  • WINDOW first determines all columns that have no corresponding outer column.
  • For every combination of existing values for these columns in WINDOW's parent context, WINDOW is evaluated, and the corresponding rows are returned.
  • WINDOW's final output is a union of these rows.
• If more than one corresponding outer column exists, an error is returned.

If all of relation's columns were added by DAX table functions, an error is returned.

If matchBy is present, WINDOW tries to use matchBy and partitionBy columns to identify the row. If matchBy isn't present and the columns specified within orderBy and partitionBy can't uniquely identify every row in relation:

• WINDOW tries to find the least number of additional columns required to uniquely identify every row.
• If such columns can be found, WINDOW automatically appends these new columns to orderBy, and each partition is sorted using this new set of orderBy columns.
• If such columns can't be found, an error is returned.

An empty table is returned if:

• The corresponding outer value of an orderBy or partitionBy column doesn't exist within relation.
• The whole window is outside the partition, or the beginning of the window is after its end.

If you use WINDOW within a calculated column defined on the same table as relation, and orderBy is omitted, an error is returned.

If the beginning of the window is before the first row, it's set to the first row. Similarly, if the end of the window is after the last row of the partition, it's set to the last row.

You can use reset in visual calculations only, and you can't use it in combination with orderBy or partitionBy. If reset is present, you can specify axis but not relation.

If the value of reset is absolute (a positive integer, HIGHESTPARENT, or a field reference) and the calculation is evaluated at or above the target level in the hierarchy, the calculation resets for each individual element. That is, the function is evaluated within a partition containing only that specific element.

Examples in this article can be used with the sample Adventure Works DW 2020 Power BI Desktop model. To get the model, see DAX sample model.

Example 1 - measure

The following measure:

3-day Average Price =
AVERAGEX (
    WINDOW (
        -2,
        REL,
        0,
        REL,
        SUMMARIZE ( ALLSELECTED ( 'Sales' ), 'Date'[Date], 'Product'[Product] ),
        ORDERBY ( 'Date'[Date] ),
        KEEP,
        PARTITIONBY ( 'Product'[Product] )
    ),
    CALCULATE ( AVERAGE ( Sales[Unit Price] ) )
)

Returns the 3-day average of unit prices for each product. The 3-day window consists of three days on which the product had sales, not necessarily three consecutive calendar days.

Example 2 - measure

The following measure:

RunningSum =
SUMX (
    WINDOW (
        1,
        ABS,
        0,
        REL,
        ALLSELECTED ( 'Date'[Fiscal Year], 'Date'[Month Number Of Year] ),
        PARTITIONBY ( 'Date'[Fiscal Year] )
    ),
    [Total Sales]
)

Returns the running sum for Total Sales by Month Number Of Year, restarting for every Fiscal Year:

Example 3 - visual calculation

The following visual calculation DAX query:

TotalSalesRunningSumByYear =
SUMX ( WINDOW ( 0, ABS, 0, REL, ROWS, HIGHESTPARENT ), [SalesAmount] )

Returns the cumulative total sales by month, calculated along each year. The value 1 could be used instead of HIGHESTPARENT, with the same result.

The screenshot below shows the visual matrix and the visual calculation expression:

Example 4 - visual calculation

The following visual calculation DAX query:

TotalSalesRunningSumByQuarter =
SUMX ( WINDOW ( 0, ABS, 0, REL,, -1 ), [SalesAmount] )

Returns the cumulative total sales by month, calculated along each quarter. The value LOWESTPARENT could be used instead of -1, with the same result.

Related content

• INDEX
• MOVINGAVERAGE
• OFFSET
• ORDERBY
• PARTITIONBY
• RANGE
• RANK
• ROWNUMBER
• RUNNINGSUM