OFFSET

OFFSET is a DAX function that retrieves a single row from a table by moving a specified number of rows within a partition sorted by a specified order or axis.

Syntax

OFFSET(
   Delta,
   Relation,
   OrderBy,
   Blanks,
   PartitionBy,
   MatchBy,
   Reset
)

Argument	Properties	Description
Delta		The number of rows before or after the current row from which to obtain the data. Can be a positive or negative value.
Relation	Optional	A table expression from which the output row will be returned. If omitted, OrderBy must be explicitly specified.
OrderBy	Optional	Columns that define how each partition is sorted. If omitted, Relation must be explicitly specified.
Blanks	Optional	Defines how to handle BLANK OrderBy values. Valid value is DEFAULT for now.
PartitionBy	Optional	Columns that define how Relation is partitioned.
MatchBy	Optional	Columns that define how the current row is identified.
Reset	Optional	Specifies how the calculation restarts. Valid values are: None, LowestParent, HighestParent, or an integer.

Return Values

One or more rows from relation.

Remarks

Except for columns added by DAX table functions, each column in relation, when matchBy is not present, or each column in matchBy and partitionBy, when matchBy is present, must have a corresponding outer value to help define the current row on which to operate, with the following behavior:

• If there is exactly one corresponding outer column, its value is used.
• If there is no corresponding outer column, then:
  • OFFSET will first determine all columns that have no corresponding outer column.
  • For every combination of existing values for these columns in OFFSET’s parent context, OFFSET is evaluated and a row is returned.
  • OFFSET’s final output is a union of these rows.
• If there is more than one corresponding outer column, an error is returned.

If all of relation‘s columns were added by DAX table functions, an error is returned.

If matchBy is present, OFFSET will try to use matchBy and partitionBy columns to identify the row. If matchBy is not present and the columns specified within orderBy and partitionBy can’t uniquely identify every row in relation, then:

• OFFSET will try to find the least number of additional columns required to uniquely identify every row.
• If such columns can be found, OFFSET will automatically append these new columns to orderBy, and each partition is sorted using this new set of OrderBy columns.
• If such columns cannot be found, an error is returned.

An empty table is returned if:

• The corresponding outer value of an OrderBy or PartitionBy column does not exist within relation.
• The delta value causes a shift to a row that does not exist within the partition.

If OFFSET is used within a calculated column defined on the same table as relation, and orderBy is omitted, an error is returned.

reset can be used in visual calculations only, and cannot be used in combination with orderBy or partitionBy. If reset is present, axis can be specified but relation cannot.

The following DAX query:

DEFINE
VAR vRelation = SUMMARIZECOLUMNS ( 
                    DimProductCategory[EnglishProductCategoryName], 
                    DimDate[CalendarYear], 
                    "CurrentYearSales", SUM(FactInternetSales[SalesAmount]) 
                  )
EVALUATE
ADDCOLUMNS (
    vRelation, 
    "PreviousYearSales", 
    SELECTCOLUMNS(
        OFFSET ( 
                -1, 
                vRelation, 
                ORDERBY([CalendarYear]), 
                PARTITIONBY([EnglishProductCategoryName])
        ),
        [CurrentYearSales]
    )
)

Returns a table that summarizes the total sales for each product category and calendar year, as well as the total sales for that category in the previous year.

The following DAX query:

DEFINE
MEASURE DimProduct[CurrentYearSales] = SUM(FactInternetSales[SalesAmount])
MEASURE DimProduct[PreviousYearSales] = CALCULATE(SUM(FactInternetSales[SalesAmount]), OFFSET(-1, , ORDERBY(DimDate[CalendarYear])))
EVALUATE
SUMMARIZECOLUMNS (
    DimDate[CalendarYear],
    "CurrentYearSales", DimProduct[CurrentYearSales],
    "PreviousYearSales", DimProduct[PreviousYearSales]
)

Uses OFFSET() in a measure to return a table that summarizes the total sales for each calendar year and the total sales for the previous year.

The following DAX query:

EVALUATE
ADDCOLUMNS (
    FactInternetSales,
    "Previous Sales Amount",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                FactInternetSales,
                ORDERBY ( FactInternetSales[SalesAmount], DESC ),
                PARTITIONBY ( FactInternetSales[ProductKey] ),
                MATCHBY( FactInternetSales[SalesOrderNumber], FactInternetSales[SalesOrderLineNumber] )
            ),
            FactInternetSales[SalesAmount]
        )
)

Returns FactInternetSales table with adding a column, which indicates, for each sale, its previous sale’s amount, of the same product, in descending order of sales amount, with current sale being identified by its SalesOrderNumber and SalesOrderLineNumber. Without MATCHBY, the query would return an error since there are no key columns in FactInternetSales table.

The following visual calculation DAX query:

SalesRelativeToPreviousMonth = [SalesAmount] - CALCULATE(SUM([SalesAmount]), OFFSET(-1, ROWS, HIGHESTPARENT))

Returns the difference in total sales between each month and the previous one within the same year.

The screenshot below shows the visual matrix and the visual calculation expression:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/offset-function-dax