Downloads
DAXRanking and windowsAdvanced

OFFSET

Returns the row a set number of positions before or after the current row, in an ordered list.

Syntax
OFFSET(<delta>, [<relation>], [<orderBy>], [<blanks>])

Returns: A table holding that row. Used inside CALCULATE or another function that takes a table.

What it does

OFFSET looks at the row a set number of steps before (a negative number) or after (a positive number) the current row, in an ordered list. Used inside CALCULATE, it gives the value for the previous period. Use it to compare each period with the one before, without writing a calendar formula. Check that your version supports it.

Syntax and arguments

Example

Sales for the previous day in the selection, for a daily chart.

Previous Day Sales =
CALCULATE (
    [Total Sales],
    OFFSET (
        -1,
        ALLSELECTED ( 'Date'[Date] ),
        ORDERBY ( 'Date'[Date] )
    )
)

For each day, the formula moves back one row in the sorted list of days and uses that day's sales. The chart can then show the current day beside the one before it.

Example: a second use

The change from the previous period, using the measure above.

Change From Previous Day =
[Total Sales] - [Previous Day Sales]

A positive figure means sales rose from the previous row, and a negative figure means they fell. Read the sign carefully when the rows are not evenly spaced.

Things to watch

In business terms

Period-over-period change in a series of balances, such as the previous month's bank balance, or the prior invoice amount for a customer. It answers "what changed since the last row?" in a review.

Related functions

All DAX functions · View as Markdown · DAX fundamentals