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.
OFFSET ( <delta>, <relation>, <orderBy>, <blanks>, <partitionBy> )
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:-
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
Step-4: After that add one table visual with two fields-
Drag YearMonthnumber from Calendar table & Sales field from Order dataset.
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.
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] ) ))
Hope you enjoyed the post, you can refer Other DAX functions: DAX functions