A DAX window function that moves a given number of rows back (negative values) or forward (positive) from the current row in a sorted table, optionally split into partitions. It is commonly used to fetch the prior or next period's value.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains OFFSET in context, with comparison tables and the common traps.
Terms in this definition
- DAX
Short for Data Analysis Expressions, the formula language you write measures, calculated columns and row-level security rules in, within tabular semantic models in Power BI, Excel Power Pivot or Analysis Services.
- Window function
A function evaluated OVER a window defined by PARTITION BY, ORDER BY and optionally a frame; unlike GROUP BY aggregation it doesn't collapse rows, but adds to each one a value worked out from its related rows. Examples are RANK, ROW_NUMBER, LAG and running totals.
- VALUES
Returns in DAX the distinct column values, or table rows, still visible after filters are applied, sometimes with an extra blank entry. CALCULATE often takes the result as a table filter.
- Event
Table in Log Analytics where entries from Windows event logs are kept.
- NEXT
Restricted to visual calculations, this DAX function reads the value one step further along an axis of the visual matrix; writing OFFSET with 1 gives the same result.
Related terms
- datetimeoffset
A data type holding a date and time together with an offset from UTC between -14:00 and +14:00. The offset is lost when the value is converted to datetime2.
- MATCHBY
Used within DAX window functions such as OFFSET, WINDOW, INDEX, RANK and ROWNUMBER, this lists the columns that pin down which row is current when the data lacks a unique key.
- ORDERBY
Sets how rows are sorted within each partition. This DAX helper can only appear inside window functions like OFFSET, RANK or ROWNUMBER.
- PREVIOUS
Fetches the value of the preceding element along an axis of the visual matrix. This DAX function works only in visual calculations and is equivalent to OFFSET with -1.
- Window functions
Calculate across rows related to whichever row is current, e.g. rankings or running totals. DAX provides OFFSET, WINDOW, INDEX, RANK and ROWNUMBER, partitioned and ordered through PARTITIONBY and ORDERBY; T-SQL uses OVER, applied once GROUP BY and HAVING finish.