DAX window functions

Last checked against Microsoft documentation: 14 November 2026

Window functions let a measure pick rows by position in a sorted, grouped table (the previous row, the first row, the last three rows), so you no longer hand-build FILTER-and-EARLIER style logic.

The functions INDEX, OFFSET and WINDOW arrived in DAX in December 2022, according to SQLBI's introduction. The WINDOW page on Microsoft Learn says they work in calculated columns, calculated tables, measures and visual calculations.

Why it matters

  • Month-over-month, moving averages, running totals that restart each year, and "top product per category" become short and readable.
  • The same functions sit underneath visual calculations (RUNNINGSUM, PREVIOUS, FIRST and others are shortcuts for them), so learning them once pays twice.

The family at a glance

FunctionReturnsTypical use
INDEXOne row at an absolute position (1 is first, -1 is last)First or last row of each group
OFFSETOne row a number of steps before or after the current rowPrevious month, next month
WINDOWMany rows between two positionsMoving average, running total
RANKThe rank of the current row within an intervalRanking with ties
ROWNUMBERA unique rank for the current rowRanking with no ties

Three helpers shape all of them:

  • ORDERBY says how rows are sorted.
  • PARTITIONBY splits rows into groups that are handled separately.
  • MATCHBY says which columns identify the current row.

Reading a WINDOW call

DAX
WINDOW ( from [, from_type], to [, to_type] [, relation] [, orderBy] [, blanks] [, partitionBy] [, matchBy] [, reset] )
PartPlain meaning
from, toWhere the window starts and ends. The last row is included.
from_type, to_typeREL counts from the current row (negative is back). ABS counts from the partition's start (positive) or end (negative). The default is REL.
relationThe table of rows to walk over. If you leave it out, you must give orderBy.
orderByThe sort order inside each partition.
blanksDEFAULT, FIRST or LAST: where blanks sort.
partitionByThe groups. If you leave it out, the whole relation is one group.
matchByThe columns that identify the current row.
resetVisual calculations only. Cannot be combined with orderBy or partitionBy.

So -2, REL to 0, REL means "the current row and the two rows before it", and 1, ABS means "the first row of the group".

Step by step: build a window measure

  1. Decide which rows you are walking over, and pick the columns that make each row unique (for example Year and Month Number).
  2. Put those columns in the relation, usually inside ALLSELECTED so the walk ignores the row's own filters.
  3. Choose the sort with ORDERBY.
  4. Choose the groups that restart with PARTITIONBY, if any.
  5. Choose the position: REL for "around the current row", ABS for "from the start or end".
  6. Wrap it: CALCULATE for single-row functions (INDEX, OFFSET), and an iterator such as AVERAGEX or SUMX for WINDOW.
  7. Test in a matrix that shows the same columns you used in the relation.

Examples

These three are original and were not run in Power BI Desktop. Table and measure names are placeholders.

DAX
-- Previous month's sales (OFFSET, one step back)
Sales PM =
CALCULATE (
    [Total Sales],
    OFFSET (
        -1,
        ALLSELECTED ( 'Date'[Year], 'Date'[Month Number] ),
        ORDERBY ( 'Date'[Year], ASC, 'Date'[Month Number], ASC )
    )
)

-- Average of this month and the two before it (WINDOW)
Sales 3M Avg =
AVERAGEX (
    WINDOW (
        -2, REL,
        0, REL,
        ALLSELECTED ( 'Date'[Year], 'Date'[Month Number] ),
        ORDERBY ( 'Date'[Year], ASC, 'Date'[Month Number], ASC )
    ),
    [Total Sales]
)

-- Sales of the best product in each category (INDEX, sorted high to low)
Top product sales =
CALCULATE (
    [Total Sales],
    INDEX (
        1,
        ALLSELECTED ( 'Product'[Category], 'Product'[Product] ),
        ORDERBY ( [Total Sales], DESC ),
        ,
        PARTITIONBY ( 'Product'[Category] )
    )
)

Microsoft's own example adds a running sum that restarts every fiscal year. It uses ABS for the start and REL for the end, and PARTITIONBY for the year:

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

Watch-outs

  • Each column in the relation needs a matching outer value to identify the current row. Columns added by DAX table functions are exempt.
  • If orderBy and partitionBy do not make rows unique, WINDOW adds the fewest extra columns it needs. If it cannot, you get an error.
  • A window that starts before the first row is clipped to the first row. One that ends after the last row is clipped to the last row. If the window is entirely outside the partition, you get an empty table.
  • In a calculated column on the same table as the relation, leaving out orderBy returns an error.
  • The "3 days" in Microsoft's moving-average example are the three days that had sales, not three calendar days. Windows walk rows that exist.

Best practices

  1. Always write ORDERBY and PARTITIONBY explicitly. Relying on defaults makes the result hard to read.
  2. Build the relation from a date or dimension table, not from the fact table, so gaps in sales do not create gaps in time.
  3. Compare against a classic time-intelligence measure on a small sample before you trust a new window measure.
  4. In a visual, prefer the shortcuts from visual calculations when the logic is only needed there.

Official sources