When can I use OFFSET DAX function in Power BI

In this writing, I like to share what I have learned about OFFSET DAX function, that recently came out to the world, but not yet documented on https://learn.microsoft.com/en-us/dax/ as of today.

When I face the situation to measure date-intelligence-related business requirement, I use SAMEPERIODLASTYEAR, DATESINPERIOD, DATEADD, and so many other time intelligence DAX functions, if a business is using normal & general calendar table. Or, I use FILTER, MIN, MAX, and some other DAX functions, if a busness is using their own & specialized calendar table.

And, all of the above cases have calendar table that contains some columns that show numbers that are related to other column. For instance, 202101 is Jan-2021, 202112 is Dec-2021, 202201 is Jan-2022, 202202 is Feb-2022, and so forth. These numbers might help to sort Month-Year column properly, but it does not help much when authoring DAX measures in Power BI.

If a calendar table has columns that have sequencially increasing integers, then these columns can help other columns in the calendar table to make date-intelligence-related DAX calculation easier. For instance, Jan-2021 is 1, Dec-2021 is 12, Jan-2022 is 13, Feb-2022 is 14, and so forth.

However, creating columns that have sequencially increasing intergers is not a easy task. Especially when it comes to the situation that you need to create this for ISO-Week-Year column.

There are some M codes out there to create a calendar table in Power Query Editor that contains sequencially increasing interger column. But it still creates additional columns.

OFFSET DAX function helps the situation when a calendar table does not have sequencially increasing interger columns.

I have a sample datamodel like below. I purposely created the sample like below where I cannot create simple DAX functions to provde previous month quantity.

Datamodel

Calendar table (does not have date column)

Sales table

Table Visualization

I cannot use DATEADD or PREVIOUSMONTH DAX function, because there is no date column.

I cannot simply minus one by using [Fiscal Month Year Sort] column, because the gap between 201912 and 202001 is not one.

By using OFFSET DAX function, I could provide previous month quantity in a relatively easy way.

One very important thing to keep in mind is that, because the [Fiscal Month-Year] column is sorted by [Fiscal Month Year Sort] column, authors have to consider carefully which column comes where, otherwise, it will show wierd result.

To summarize, OFFSET DAX function helps not to have additional columns in a calendar table that show sequencially increasing integers.

There are a lot more things to learn about it, but for now, I hope this helps having fun with OFFSET DAX function.