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,FIRSTand others are shortcuts for them), so learning them once pays twice.
The family at a glance
| Function | Returns | Typical use |
|---|---|---|
INDEX | One row at an absolute position (1 is first, -1 is last) | First or last row of each group |
OFFSET | One row a number of steps before or after the current row | Previous month, next month |
WINDOW | Many rows between two positions | Moving average, running total |
RANK | The rank of the current row within an interval | Ranking with ties |
ROWNUMBER | A unique rank for the current row | Ranking with no ties |
Three helpers shape all of them:
ORDERBYsays how rows are sorted.PARTITIONBYsplits rows into groups that are handled separately.MATCHBYsays which columns identify the current row.
Reading a WINDOW call
WINDOW ( from [, from_type], to [, to_type] [, relation] [, orderBy] [, blanks] [, partitionBy] [, matchBy] [, reset] )| Part | Plain meaning |
|---|---|
from, to | Where the window starts and ends. The last row is included. |
from_type, to_type | REL counts from the current row (negative is back). ABS counts from the partition's start (positive) or end (negative). The default is REL. |
relation | The table of rows to walk over. If you leave it out, you must give orderBy. |
orderBy | The sort order inside each partition. |
blanks | DEFAULT, FIRST or LAST: where blanks sort. |
partitionBy | The groups. If you leave it out, the whole relation is one group. |
matchBy | The columns that identify the current row. |
reset | Visual 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
- Decide which rows you are walking over, and pick the columns that make each row unique (for example Year and Month Number).
- Put those columns in the relation, usually inside
ALLSELECTEDso the walk ignores the row's own filters. - Choose the sort with
ORDERBY. - Choose the groups that restart with
PARTITIONBY, if any. - Choose the position:
RELfor "around the current row",ABSfor "from the start or end". - Wrap it:
CALCULATEfor single-row functions (INDEX,OFFSET), and an iterator such asAVERAGEXorSUMXforWINDOW. - 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.
-- 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:
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
orderByandpartitionBydo not make rows unique,WINDOWadds 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
orderByreturns 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
- Always write
ORDERBYandPARTITIONBYexplicitly. Relying on defaults makes the result hard to read. - Build the relation from a date or dimension table, not from the fact table, so gaps in sales do not create gaps in time.
- Compare against a classic time-intelligence measure on a small sample before you trust a new window measure.
- In a visual, prefer the shortcuts from visual calculations when the logic is only needed there.