OFFSET

The row a number of rows before or after the current one, within its partition and order.

Group: Window and visual calculations. Compatible with DAX.

Syntax

OFFSET ( <delta> [, <relation or axis>] [, <orderBy>] [, <blanks>] [, <partitionBy>] [, <matchBy>] [, <reset>] )

Parameter	Type	Description
`delta`	Whole number	How many rows to move: negative back, positive forward, 0 the current row.
`relation or axis`	Table optional	The rows it works among: a table of the model or one the query defines. Left out, the values the ORDERBY and PARTITIONBY columns have in the selection (as `ALLSELECTED` gives them). In a visual calculation, an axis such as `ROWS` instead.
`orderBy`	ORDERBY optional	`ORDERBY ( ... )`: how the rows of each partition are ordered. Left out, by all the relation's columns, smallest first.
`blanks`	Keyword optional	Where blank values go in the order: `DEFAULT` (or `KEEP`), the only choice here outside a visual calculation.
`partitionBy`	PARTITIONBY optional	`PARTITIONBY ( ... )`: columns of the relation that split it into groups; the function stays inside the current row's group.
`matchBy`	MATCHBY optional	`MATCHBY ( ... )`: the columns that identify the current row. Left out, all the relation's columns.
`reset`	Reset optional	In a visual calculation only: where the count starts again, as for `PREVIOUS`.

Returns

Table. The relation's row that many rows from the current one, with the relation's columns.

Remarks

The usual use is as a filter: CALCULATE ( [Liters sold], OFFSET ( -1, ALLSELECTED ( Sales[Month] ), ORDERBY ( Sales[Month] ) ) ) is the month before's liters.
Past the edge of the partition there is no row: the table is empty, and a measure filtered by it is BLANK.
Rows that tie in the order are ordered further by the relation's other columns, so the order is always complete.
The current row comes from the context. Where the context leaves several rows, at a total, OFFSET returns the rows for each of them together: on a grand total, the previous month of every month at once.
A table made with DATATABLE or { } is refused as the relation, as nothing tells its rows apart. Define it with DEFINE TABLE in the query, or use a table of the model.
In a visual calculation the relation can be an axis (ROWS, COLUMNS) and a reset can follow: CALCULATE ( SUM ( [Liters] ), OFFSET ( -1, ROWS ) ) gives what PREVIOUS ( [Liters] ) gives.

Examples

The month before

EVALUATE
SUMMARIZECOLUMNS (
    Sales[Month],
    FILTER ( ALL ( Sales[Month] ), YEAR ( Sales[Month] ) = 2026 && MONTH ( Sales[Month] ) <= 4 ),
    "Liters", [Liters sold],
    "Month before",
        CALCULATE (
            [Liters sold],
            OFFSET ( -1, ALLSELECTED ( Sales[Month] ), ORDERBY ( Sales[Month] ) )
        )
)
ORDER BY Sales[Month]

Sales[Month]	Liters	Month before
2026-01-01	45549	(blank)
2026-02-01	45985	45549
2026-03-01	45109	45985
2026-04-01	48416	45109

The month before, product by product

PARTITIONBY keeps each product to its own months.

EVALUATE
SUMMARIZECOLUMNS (
    Sales[Month],
    Sales[Product],
    FILTER ( ALL ( Sales[Month] ), YEAR ( Sales[Month] ) = 2026 && MONTH ( Sales[Month] ) <= 3 ),
    FILTER ( ALL ( Sales[Product] ), Sales[Product] = "IPA" || Sales[Product] = "Pils" ),
    "Liters", [Liters sold],
    "Month before",
        CALCULATE (
            [Liters sold],
            OFFSET ( -1, ALLSELECTED ( Sales[Month], Sales[Product] ), ORDERBY ( Sales[Month] ), PARTITIONBY ( Sales[Product] ) )
        )
)
ORDER BY Sales[Product], Sales[Month]

Sales[Month]	Sales[Product]	Liters	Month before
2026-01-01	IPA	8239	(blank)
2026-02-01	IPA	8112	8239
2026-03-01	IPA	8958	8112
2026-01-01	Pils	25329	(blank)
2026-02-01	Pils	25749	25329
2026-03-01	Pils	24056	25749

On a table the query defines

B and C tie at 80 liters; the shop's name puts B first.

DEFINE
    TABLE Shops =
        DATATABLE (
            "Shop", STRING, "Liters", INTEGER,
            { { "A", 50 }, { "B", 80 }, { "C", 80 }, { "D", 20 } }
        )
EVALUATE
ADDCOLUMNS (
    Shops,
    "Shop before",
        MAXX ( OFFSET ( -1, Shops, ORDERBY ( Shops[Liters] ) ), Shops[Shop] )
)
ORDER BY Shops[Liters], Shops[Shop]

Shops[Shop]	Shops[Liters]	Shop before
D	20	(blank)
A	50	D
B	80	A
C	80	B

WINDOW, INDEX, PREVIOUS, NEXT, ORDERBY.

Last updated 3 November 2026 · OpenBI Studio 0.3.74