An Attempt to Use New OFFSET Function in DAX to Find Previous/Next Values in a Visual in Power BI

It's a bit strange to write about a new DAX function, that doesn't yet appear even in the intellisense in Power BI Desktop! And also there is no documentation about it yet! What tempted me is what I saw on Twitter last week, here and here!

In a retweet from Chris Webb , a bunch of DAX code showing values for previous row in a matrix without relying on index-like solutions! (Original tweet by Takeshi Kagata )

Since DAX does not have a cell-based reference like Excel, it's not always easy to capture values from previous row in a visual. Most common methods include using index-columns or using functions such as EARLIER.

I wrote a blog post almost 3 years ago, on my blog PowerBI.Istanbul, showing how to find previous week sales value in a matrix, where I ended up with 8 lines of DAX code, like this one:

Previous Week Sales :=
IF( HASONEVALUE( 'Calendar'[Week No] ) ,
? ? CALCULATE( [Sales] ,
? ? ? ? FILTER( ALL( 'Calendar' ) ,
? ? ? ? 'Calendar'[Week Index] = MAX( 'Calendar'[Week Index] ) - 1 
? ? ? ? )
? ? )
)         

HASONEVALUE part of the code is just to show measure only at week no level. The rest of the code relies on indexing logic set on additional Week Index column, where I assign unique values for each and every week in Calendar table. Not a rocket science if you are familiar with DAX and you know how iterators work!

Similar previous week calculation is now also achievable using OFFSET function. The code below will calculate the same values, just in a different way. (Red lines just indicate that intellisense doesnt recognize it yet!)

I didn't make comprehensive tests, but the performance of these 2 different measures are quite similar, at least for my dataset. (I use Contoso database from MS)

So the question is, if it's already achievable with the current DAX abilities, what does that brand new DAX function bring us new! Well, if you are able to setup indexing logic like the one above, then fine, you may continue using it! But how about the matrix below?

It's a piece of cake if we are using Excel, but not in DAX!

You have to setup some sort of logic to achieve this and it's quite likely that your DAX code won't be a short one!

The code below, with the help of new OFFSET function will do this for us!

I noticed that removing the ORDERBY line from the code doesn't make any difference, so it should be an optional paramater. However, that ORDERBY part has also an option to choose the direction of calculation!

I'm a little bit confused at this point, because I'm not able to figure out what to do if user changes the sort order from the visual! For example, if user sorts the matrix according to Sales measure in increasing order, then the matrix looks like this!

To my knowledge, there is no way to capture how the visual is sorted as of today!

...

I read the following in one of the responses to the tweet, from Jeroen (Jay) ter Heerdt , member of Power BI CAT team!

"Relative movement in the result set" ! That's a bold sentence!

I'm keen to find out more about this new DAX function! Eagerly waiting for more information on this.

Cheers.