OFFSET
Returns the row a set number of positions before or after the current row, in an ordered list.
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
- delta: how many rows to move. -1 is the previous row, and 1 is the next row.
- relation (optional): the rows to consider, usually
ALLSELECTEDof a column the visual uses. - orderBy (optional):
ORDERBYof the column that sets the sequence. - blanks (optional): how blank rows are treated. See the Microsoft reference.
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
- The sort order decides which row is "previous." Without the right
ORDERBY, the result is wrong. - Gaps in the list matter. If a day or month has no rows, the previous row may not be the one you expect. Test with a known gap.
- For the same period last year, use time intelligence functions instead.
- Confirm that your version of Power BI supports the function.
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.