OFFSET DAX Function

OFFSET allows you to perform comparison calculations more easily by retrieving a row that is in a relative position from your current position. In short “Comparing values against a previous value”

Its comes under Filter functions Dax category. Also, you can use two helper functions ORDERBY and PARTITIONBY with OFFSET Function.

Syntax

OFFSET ( <delta>, <relation>, <orderBy>, <blanks>, <partitionBy> )

Parameters

delta – The number of rows before (negative value) or after (positive value) the current row from which to obtain the data. It can be any DAX expression that returns a scalar value.

relation -(Optional) A table expression from which the output row is returned.

orderBy -(Optional) An ORDERBY() clause containing the columns that define how each partition is sorted.

blanks -(Optional) An enumeration that defines how to handle blank values when sorting.

partitionBy -(Optional) A PARTITIONBY() clause containing the columns that define how <relation> is partitioned.

Let’s start with an example,  you can download the sample Dataset from below link:-

• Global Super Store Dataset

Following the below steps-

Step-1: Load Global Super store data.

Step-2: Create a Calendar Table using DAX function, Refer this Crate a calendar table

Step-3: Now create a relationship between Calendar table and Order Dataset

Relationship

Step-4: After that add one table visual with two fields-

Drag YearMonthnumber from Calendar table & Sales field from Order dataset.

Table-visual

Step-5: Create a measure with below DAX formula

Offset =
CALCULATE (
    SUM ( Orders[Sales] ),
    OFFSET ( -1, , ORDERBY ( 'Calendar Table'[YearMonthnumber] ) )
)

Step-6: Now add that measure to table visual and see the result. Here you can compare the current value with previous value.

Offset-DAX

Use OFFSET with PARTITIONBY

Here you will see how to partition result year wise-

Offset =
CALCULATE (
SUM ( Orders[Sales] ),
OFFSET ( -1, , ORDERBY ( 'Calendar Table'[YearMonthnumber] ), ,
PARTITIONBY('Calendar Table'[Year] )
))

Offset partitionby

Hope you enjoyed the post, you can refer Other DAX functions: DAX functions