Advanced DAX Window Functions: OFFSET, INDEX, ORDERBY, PARTITIONBY

1. Function Syntax Reference

The three core window functions share a common calling convention. They all accept a relation expression (usually an existing table or a SUMMARIZECOLUMNS result) and optional ORDERBY, PARTITIONBY, and MATCHBY clauses.

OFFSET(
    delta,
    [relation],
    [ORDERBY(<column1>, [sort_order1], <column2>, [sort_order2], ...)],
    [PARTITIONBY(<column1>, <column2>, ...)],
    [MATCHBY(<column1>, <column2>, ...)]
)

INDEX(
    position,
    [relation],
    [ORDERBY(...)],
    [PARTITIONBY(...)],
    [MATCHBY(...)]
)

WINDOW(
    from,
    from_type,
    to,
    to_type,
    [relation],
    [ORDERBY(...)],
    [PARTITIONBY(...)],
    [MATCHBY(...)]
)

The sort_order values are ASC and DESC. The from_type and to_type values in WINDOW are ABS (absolute position) and REL (relative offset). Omitted parameters default to the visual filter context, which is usually what you want in a measure.

2. OFFSET: Previous Period Comparison

The most common use case for OFFSET is comparing the current row to the previous row. Consider a matrix visual with Year on the rows and Revenue as the measure. The classic year-over-year calculation using OFFSET is:

Revenue Prior Year =
CALCULATE(
    [Total Revenue],
    OFFSET(
        -1,
        ALLSELECTED('Calendar'[Year]),
        ORDERBY('Calendar'[Year], ASC)
    )
)

Revenue YoY Growth % =
DIVIDE(
    [Total Revenue] - [Revenue Prior Year],
    [Revenue Prior Year]
)

OFFSET(-1) navigates one position back in the ordered list of years visible in the visual. ALLSELECTED respects the user's slicer selections. The pattern gracefully handles non-contiguous year selections where SAMEPERIODLASTYEAR would fail or return blank.

This pattern extends naturally to comparisons against arbitrary prior periods. OFFSET(-2) gives you two periods back. OFFSET(1) looks forward one period, which is useful for showing the next-period forecast alongside actuals.

3. INDEX: First, Last, and Nth Position

INDEX returns data from an absolute position within the partition. INDEX(1) returns the first row. INDEX(-1) returns the last. This is ideal for period-start and period-end measures.

Revenue at Year Start =
CALCULATE(
    [Total Revenue],
    INDEX(
        1,
        ALLSELECTED('Calendar'[Date]),
        ORDERBY('Calendar'[Date], ASC),
        PARTITIONBY('Calendar'[Year])
    )
)

Revenue at Year End =
CALCULATE(
    [Total Revenue],
    INDEX(
        -1,
        ALLSELECTED('Calendar'[Date]),
        ORDERBY('Calendar'[Date], ASC),
        PARTITIONBY('Calendar'[Year])
    )
)

The PARTITIONBY('Calendar'[Year]) clause resets the index within each year. Without it, INDEX would return the absolute first and last row across all selected years, which is almost never what you want.

4. WINDOW: Running Totals and Moving Averages

WINDOW returns a set of rows defined by from and to boundaries. It is the building block for running totals, moving averages, and rolling windows.

Running Total =
CALCULATE(
    [Total Revenue],
    WINDOW(
        1, ABS,
        0, REL,
        ALLSELECTED('Calendar'[Date]),
        ORDERBY('Calendar'[Date], ASC)
    )
)

Trailing 12 Month Revenue =
CALCULATE(
    [Total Revenue],
    WINDOW(
        -11, REL,
        0, REL,
        ALLSELECTED('Calendar'[Month End]),
        ORDERBY('Calendar'[Month End], ASC)
    )
)

Rolling 3 Month Avg =
DIVIDE(
    [Trailing 3 Month Revenue],
    3
)

The first WINDOW expression uses from=1 ABS (absolute position 1) and to=0 REL (current row), producing a classic running total from the first selected date up to and including the current date. The T12M pattern uses -11 REL to 0 REL for a rolling window of the current row plus the 11 preceding rows.

5. PARTITIONBY: Resetting Calculations Per Group

PARTITIONBY divides the relation into groups, and window calculations are computed independently within each group. This is essential for comparisons where each group has its own ordering.

Rank Within Region =
RANK(
    ALLSELECTED('Customer'[Customer Name]),
    ORDERBY([Total Revenue], DESC),
    PARTITIONBY('Customer'[Region])
)

Revenue vs Top Customer in Region =
VAR TopRev =
    CALCULATE(
        [Total Revenue],
        INDEX(
            1,
            ALLSELECTED('Customer'[Customer Name]),
            ORDERBY([Total Revenue], DESC),
            PARTITIONBY('Customer'[Region])
        )
    )
RETURN
    DIVIDE([Total Revenue], TopRev)

The first measure ranks customers within each region. The second measure expresses each customer's revenue as a percentage of the top customer in the same region, which is a common pattern for sales territory analysis.

6. MATCHBY: Disambiguating in Complex Visuals

MATCHBY is necessary when the visual contains columns that are neither in ORDERBY nor PARTITIONBY and the engine cannot unambiguously align the current row to the windowed relation. The most common case is a matrix with rows on one axis and columns on another.

Revenue Prior Month (Matrix Visual) =
CALCULATE(
    [Total Revenue],
    OFFSET(
        -1,
        ALLSELECTED('Calendar'[Year], 'Calendar'[Month]),
        ORDERBY('Calendar'[Year], ASC, 'Calendar'[Month], ASC),
        MATCHBY('Calendar'[Year], 'Calendar'[Month])
    )
)

Without MATCHBY, the OFFSET call may navigate incorrectly when the visual has Year on rows and Month on columns. MATCHBY tells the engine which keys uniquely identify the current row in the visual grid. Use MATCHBY whenever your visual spans two or more hierarchical dimensions.

7. RANK and ROWNUMBER: Companion Functions

RANK and ROWNUMBER accompany the window functions and share the same ORDERBY and PARTITIONBY vocabulary. RANK returns 1-based ranks with ties receiving equal ranks. ROWNUMBER assigns unique sequential integers.

Customer Rank =
RANK(
    DENSE,
    ALLSELECTED('Customer'[Customer Name]),
    ORDERBY([Total Revenue], DESC)
)

Row Number =
ROWNUMBER(
    ALLSELECTED('Customer'[Customer Name]),
    ORDERBY([Total Revenue], DESC)
)

RANK has three tie-breaking modes: DENSE, SKIP, and the default which is SKIP. ROWNUMBER never produces ties. Use RANK when you care about ordinal position with ties. Use ROWNUMBER for pagination, deduplication, or anywhere you need a unique sequential index.

8. Performance Considerations

Window functions typically outperform equivalent EARLIER-based patterns, but there are performance pitfalls specific to WINDOW.

• Materialization cost: WINDOW must construct the windowed subset of rows for every visible row. For large datasets with unbounded windows, this can materialize millions of intermediate rows.
• Relation sizing: always pass the smallest appropriate relation. Use ALLSELECTED on the specific columns that define the window rather than entire tables.
• Sort stability: ORDERBY on non-unique columns can produce non-deterministic results across refreshes. Always include a tiebreaker column if uniqueness is not guaranteed by your primary sort.
• Visual vs query context: in complex visuals, measure evaluation may happen thousands of times. Use variables aggressively to avoid repeated window evaluation.

For performance tuning and measure profiling, see our DAX Studio guide for enterprises.

9. Migrating Legacy Patterns

If you inherit a model with EARLIER-based running totals or complex FILTER-ALL CALCULATE tricks, migration to window functions usually produces cleaner and faster code. Here is a typical before-and-after.

-- BEFORE: legacy running total pattern
Running Total Legacy =
CALCULATE(
    [Total Revenue],
    FILTER(
        ALL('Calendar'[Date]),
        'Calendar'[Date] <= MAX('Calendar'[Date])
    )
)

-- AFTER: window function pattern
Running Total Modern =
CALCULATE(
    [Total Revenue],
    WINDOW(
        1, ABS,
        0, REL,
        ALLSELECTED('Calendar'[Date]),
        ORDERBY('Calendar'[Date], ASC)
    )
)

The modern pattern is more declarative, easier to read, and usually faster. In one recent engagement, migrating 40 legacy running-total measures reduced average report load time by 32 percent without changing the data model.

Frequently Asked Questions