Over the past few days, I attended the Power BI Next Step conference in Lego land – Denmark. During the keynote, Will Thompson – PM on the Power BI team, showed a new DAX function that is available to all of us already, but was very well hidden in the latest builds of Power BI Desktop. This new function, called OFFSET, allows us to do in context comparisons between two values, without writing extremely lengthy and complex DAX.

I gave it a go and in this post I share my first experiences with this new function and how I think this will make our life easier!

How I got to know about OFFSET

As mentioned in the introduction of this blog, I attended the Power BI Next Step conference! A great experience where I learned a lot, engaged with likeminded people in the community and above all had an awesome time! (Big credits to the organizers!)

As part of this conference, Will Thompson – Program Manager at Microsoft, together with Ewan Tinitali – Designer in the Power BI team, came over from the US to run an amazing Keynote to kick-off the conference. During this keynote they shared many insights into where Power BI is headed towards in the future. As part of that, Will showed a new DAX function called OFFSET.

Some amazing new announcements at the #powerbinextstep conference by @Will_MI77 – a new OFFSET DAX function (and EVALUATEANDLOG for DAX debugging), visual level calculations and visual level format panes! pic.twitter.com/agfRirxt0n

— Rishi Sapra MVP (@rsaprano) September 20, 2022

Besides all the other cool announcements, I will only talk about OFFSET now. In the replies of above tweet, Jeroen ter Heerdt (Jay) – Program Manager at the Power BI team also confirmed that this new DAX function is available in the most recent versions of Power BI Desktop and allows you to calculate “relative movement in a result set”.

It is in the product, no intellisense yet. Basically allows for relative movement in the result set.

— Jeroen (Jay) ter Heerdt #PowerBI (@JayPowerBI) September 20, 2022

Use case for OFFSET

So, this new thing, called OFFSET, what is it and is there anything similar already? The best comparison that I could find, is what we used to do with time intelligence. There are many DAX functions to calculate in the context of time, like DATESMTD or DATESYTD. If we build a visual in Power BI, we can easily calculate the differences by using these functions. However, in case you want to do something similar by calculating the difference with the previous row in a visual, this will be much harder and you must write some complex DAX. Well, not anymore!

Another easy comparison that can be made, is what we used to do in a Excel spreadsheet for example. In Excel it is pretty simple to just calculate the difference between two cells like the example below.

Doing something similar in Power BI, now just got a lot easier with OFFSET!

Using OFFSET

OFFSET, is not fully implemented in the recent builds of Power BI yet, however you can start using it already. IntelliSense does not work yet, so while you’re writing the DAX expression, it will show you an error and red-underlined items. But if you just continue building the DAX expression using this function, it just works!

OFFSET uses three parameters to work.

• Direction – number (-1 means go up, 1 means go down etc.)
• Column specification on which you want to calculate the difference – table specification
• Order direction, also partitioned by parameter (optional)

In the order direction there is another new DAX Function introduced, being ORDERBY. The ORDER BY function has two parameters, being:

• Column reference
• Order direction – ASC or DESC

In the example I setup, I calculate the difference in number of orders between the product in the current row context and the row above. To do this, I setup the following DAX expression where I used a variable to first calculate the value from the previous row and finally compare that with the current row.

$ Orders difference previous row = 
VAR PreviousRow = 
    CALCULATE(
        [# Orders],
        OFFSET(
            -1, 
            ALLSELECTED('Product'[Product]),
            ORDERBY('Product'[Product], ASC)
        )
    )
RETURN
    [# Orders] - PreviousRow 

In above example, I compare with the previous row, therefore I set the direction parameters to -1. In the column specification, I only wanted to use the products in the current selection. If other filters are applied, this set will change, therefore I used the ALLSELECTED ( ‘Product'[Product] ). Finally, in the order direction, I define the column used in the visualization and in which direction the products in this example should be ordered to define what the first row will be. In the ORDERBY function, I specified once more the Product table and column and order it ascending by using ASC.

As a result, I can now create the following visualization where I can do a row-over-row comparison.

I set the first parameter in the OFFSET function to -1, but you can obviously play around a bit by changing it to a positive number or even a higher number to compare to a few rows up/down.

In my example, I compared different products against each other to keep it simple, but of course there are much more valuable and relevant use cases. You can specify the column easily in the OFFSET function to calculate over a different visual. I think this is a huge step forward in the ease of using DAX, where it will be much easier to build complex calculations like these in a simple way! For your reference, I have uploaded a sample Power BI desktop file to my GitHub repository for Power BI Samples.

As a final and important note, since this DAX function is officially not out yet (September 2022), there won’t be official support if things break due to using this function. Also, the way how this function works might still be subject to change. So keep an eye out on the official Power BI blog for the announcement once it will be released and supported.