NEW OFFSET Function in DAX | Will It Change How You Write DAX?

Hi, thanks for the amazing tutorial! I am trying to use the offset function to calculate the previous calendar week in a calculation group. It does not work, do you have an idea on how it works?

Thanks so much for the clear explanation on how to use OFFSET.... I think you just changed my life!

I clicked because I thought this was about Cardi B's husband, Offset.

Will this offset function work if for example do something like CurrentColumn = var previousevalue = Calculate([CurrentColumn], Offset(-1)).

great job, thanks very much! I appreciate the effort with the work arounds and also the candid assessment of it's usefulness.

Thanks. 🍓

So can you use this to evaluate a result in a row based on the result in the row above, of the same column. practical example case is when you are calculating projected stock, you need to refer the calculation to the projected stock of the previous month to evaluate the projected stock of the month. WOULD APPRECIATE YOUR ASSIST.

you are genius, how do you decide, when we should select All, SelectedAll, SelectedValue, Summarize, or Summarize COlumns
do you have case studies for these?

Hey bro, for the multiple fields part, can we simply include the desired field in relation and orderby arguments instead of partition?

I love how you explain things by showing the Excel equivalent, then showing how the function works in Power BI. I tried the OFFSET function and it worked great! Seems like an alternative to the prior pattern, FILTER(ALL(Time.

Is there any other option for gaining the same result, especially for the month/total sales example, beside the offset - orderby functions, since my version of power BI can't recognize it?

Great

One of the best explanation about OFFSET. Thank You.

Hello, thanks for your video, please i have a doubt, i try to create measure, all is good but the total is empty, this the code with offset, thanks very much

TEXTO_ANTERIOR =
VAR FILA =
SUM(ejemplo[TEXTO])
VAR fila_anterior =
CALCULATE(
sum(ejemplo[TEXTO]),
OFFSET(
-1,
ALL(ejemplo),
ORDERBY(ejemplo[FECHA_EVENTO_DATE_HORA], ASC)
)
)

VAR previous_corregida=

IF(fila_anterior=fila,0,FILA)


RETURN
previous_corregida

This is by far the best explanation about offset! It reminds us of window functions in SQL (LAG and LEAD) as somebody wrote it! I also liked your workarounds!

Wonder if it can be used to calculate rolling totals that are "complex"

Can the first param of OFFSET() be a scalar valued expression? Or does it have to be an explicit fixed constant?

This does not work for multiple group bys

Hey pal i am big fan of yours, "POWER BI"☺ i have a question about power query we can undo(means delete) the step, So is there any way we can redu(means to get last deleted step) the step. Deleted step could be in any line in applied steps. I am asking because sometimes the step is big we write m query so that we just can redu the step instead of to write m query again.

Hi I'm having a error after using offset saying "It is not a valid table, variable, or function name." . Can you please tell me why this is happening🧐🧐🥸

To do it over a longer period you could combine Year & Month in one column. "YYYY-MM"

It reminds me of the infamous EARLIER function, when we try to get the previous row in a dataset; an impossible task in direct query mode. Now it's possible, even it's not the most performant solution. Power Bi is getting better!

🔥🔥🔥🔥

Is it possible to perform similar operations in matrix visual. I want to compare the value from previous cell in same row with current row to calculate difference and highlight the current cell value as green if difference is positive and red if difference is negative.

I have a matrix visual where I need to highlight the cells in a Matrix visual when value is increased or decreased compared to previous column value in same row.

Please help me if it is possible. Thank you 😊😊

Thanks Bas. Love the examples. I would just call out that while adding the total sales by manufacturer work in your example, it's not dynamic anymore and if a filter was to change the rank of the manufaturers in that filter context, it won't take that context in consideration in the result as it's pulling from the column value. So by example if a manufature ranks 2nd overall, as filters get applied, it could change to 3rd rank but would still be considered as 2nd in your measure... would love to find another workaround that would keep it fully dynamic! I think there's great potential to have fun with that new function, maybe squeezing some field parameters, calculation groups and a slicer to select the offset value on top 🙂

Thank you for this great video !

Is there a way to use offset to return a text value instead of a numeric ??

this is huge, so many workaround needed before to this simple thing

Thats great that they bring it in, but i Think Old Dev will anyways use old Time Int functions are just dateadd for custom ones, it's not to big deal I think, this may be goof for new power bi users who knows offset from excel

Dear Buz, I am very fascinated by the way you manage to explain in such a simple and clear way, thousands of pages of Microsoft instructions .... !!! 😄 congratulations...!!

It would also be very interesting if you could dedicate some video tutorials about the "Power BI report builder" application to make structured reports that can be printed...Can you do It for us?
Many thanks and Bravissimooooooo 👏👍

Lovely lecture! I found your channel is the most helpful! TYTY

Thank you for this video, this function is so helpful (to be honest in Tableau different to the next value is super easy, but in PBI it was more challenging), now delta to the next is also easy in PBI, 5!

For time intelligence, couldn't you sort by YearMonth Number? Then adjust the var to IF( [YYYYMM] > MIN( [YYYYMM] ), [Total Sales] - SalesOffset )

Would concatenating year and month in a “yyyymm” format and sorting by that value for month name theoretically work for the JAN values when using the offset and orderby functions together?

Already saw this….

I can't understand how it works for manufacturer column and not for month as both are text

Good work Bas, appreciate your in-depth efforts. I like how you played around with workarounds and explored all tricks to make this work in preview.
Demonstrating what does not work, to understand its best use case, is also very important.

Amazing!!!!! The community has been crying out for this feature for a long time.

Great video! Time intelligence calculations are still frustration. I recently found a solution to such a problem like the one in your example using TREATAS and braces{} to define a table within a table. cc.Previous_Year_Patients =
VAR __previous_Year = DATE( [YEAR], 1, 1 )-1
VAR __previous_Patients =
CALCULATE(
SUM ( TB_POP[Patients]),
TREATAS( {__previous_YEAR} , TB_POP[YEAR] ),
ALLEXCEPT( TB_POP, TB_POP[Zip Code] )
)
RETURN
__previous_Patients

Here is a possible practical use - offsetting weeks, since there's no native time intelligence for that.

Interesting function to know! Thank you Bas for covering in detail, but will require a bit more time and efforts to understand and use it better. Of course, as it is yet to be released, we might see the functionality that you wished for and demonstrated with work arounds actually appear, fingers crossed!

Thanks for sharing. It will work nicely for company specific period codes, as we now easily can calculate the deviation to the previous period.

Good function, but don't we lose possibility for customer to sort any column him/herself?

Thanks Bas, it's always good to also show what doesn't work and why, so from me thanks for including these bits rather than leaving them on the cutting room floor :)

Nice analysis Bas. Was quite intrigued when I read about this a few days ago. Potentially could be great if they can get make it easy enough to use.

Thank you for sharing and providing examples and solid explansions on the usage of the new OFFSET. Totally agree that it might not be super useful currently, but shows that there is potential once it gets refined a bit. Cheers!

Great presentation

should make calculating moving ranges a lot easier, so for XMR charts will help a lot!

I think this is going to help witg the super conplex dax i put together a while back. I need to calculate the time difference between 2 time stamps. The durations are milliseconds but the difference could be seconds to days.