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.
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!
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!
As your name reminds me of a bus that makes to touch each stop while travelling. Your videos also touches every problem in that solution. That's a great thing in your videos.
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 🙂
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.
@@PeterKontogeorgis well, some of them are not where we want to take this, but it will make sense soon enough. What you are seeing is a first building block for a huge development which has not been revealed.
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 :)
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.
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 😊😊
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 👏👍
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!
logic is fine, but you would first have to calculate the min YYYYMM of the year correctly .. MIN[YYYYMM) returns the current month, [YYYYMM] will give an error as there is no row context .. you can adjust it so that this logic works, I think is just a bit more complex
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?
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.
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, 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?
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!
thanks for watching Ernest! 😊 my hope was that it would be similar to the lookup function in tableau. At the moment it is not quite there yet though as you miss the flexibility of the table calculations that tableau has (adjusting how you compute + ability to use measures for the sort)
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?
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!
It can be used in a calculated column, but I don't think it can be used to reference previous values of that same calculated column. So it won't help produce a running / cumulative total
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) ) )
Well explained Bas, the OFFSET is not an easy function in Excel. For time intelligence: would not DATEADD be a lot more easier? I think you get the same result. As for the manufacturers example, I do not see (yet?) a practical business use.
thanks Bart! 🙂 definitely would stick to using dateadd for now and I'm also struggling to find good use cases ( that's also my conlusion in the video ;) ) . If you could use measures for the orderby function that would make it very useful though. Below in the comments a suggested use case is pareto analysis (didnt try it yet).
I still used to think, what makes Microsoft so complicated for not allowing us to use direct column reference in measures and values section, which will makes dax super easier.
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
it works for both .. it is just that the month column has a "sort by" column and therefore I had to include the monthnr (the column that is use for the sortby)
The Orderby now works on Total Sales : OFFSET Manufacturer = CALCULATE( [Total Sales], OFFSET( -1, ALLSELECTED(dimProduct[Manufacturer]), ORDERBY([Total Sales], ASC), ) ) So the last part of the video is obsolete.
The OFFSET function will likely be a solid candidate for calculating delta values in previous rows, like we would in SQL with LAG(). If you want to see a demonstration of the similarities, here’s my latest video on the functionality: ruclips.net/video/QYgi9Epzwsc/видео.html
I was thinking about that one as well, but the thing is that you would still need an weekid field (that would continue in the second year with week 53, 54, 55 etc) and offset it. This you could also do without it and using the offset function would be worse when you have a hierarchy (year and week on axis) as the first value would be blank
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
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🧐🧐🥸
One of the best explanation about OFFSET. Thank You.
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.
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!
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!
Glad to hear this Ozan, thx!
As your name reminds me of a bus that makes to touch each stop while travelling. Your videos also touches every problem in that solution. That's a great thing in your videos.
lol 😂 my parents will be happy to hear they picked the right name for me 😁 thanks Narendra
@@HowtoPowerBI haha ❤
this is huge, so many workaround needed before to this simple thing
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 🙂
Thanks so much for the clear explanation on how to use OFFSET.... I think you just changed my life!
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.
Much appreciated! thanks for watching and the nice words 😊
Oh and one mote: - 1 actually moves up one. Not down as you said in your first Power BI demo. Negative is above, positive is below
Mote == more
The function turned my world upside down 😉 lol
@Dutch Data Dude Now you need to go back through Bas's previous video's and create dev tasks for all the suggestions he's made :)
@@PeterKontogeorgis well, some of them are not where we want to take this, but it will make sense soon enough. What you are seeing is a first building block for a huge development which has not been revealed.
@@dutchdatadude thanks for the reply ...was more referring to suggestions from previous videos. And half in jest :)
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 :)
Thank you very much. I really appreciate the way you teaching, and the level of dedication you make your videos with
that's great to hear, happy it helps 😊
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.
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 😊😊
Wonder if it can be used to calculate rolling totals that are "complex"
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 👏👍
Thanks for sharing. It will work nicely for company specific period codes, as we now easily can calculate the deviation to the previous period.
I was thinking the same thing. For accounting periods perhaps it would work well.
Lovely lecture! I found your channel is the most helpful! TYTY
thank you so much Hosung 😊
You are saving my project legend, thanks a mill
Wow …. Understanding dax before release. Great Vedio Bas and use case was also great. Kudos to your hard work 😊 I eagerly wait for your vedios
thanks for watching Sarvesh! 😊yea, it's nice that we can play around with the function already before its official release
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!
Can the first param of OFFSET() be a scalar valued expression? Or does it have to be an explicit fixed constant?
For time intelligence, couldn't you sort by YearMonth Number? Then adjust the var to IF( [YYYYMM] > MIN( [YYYYMM] ), [Total Sales] - SalesOffset )
logic is fine, but you would first have to calculate the min YYYYMM of the year correctly .. MIN[YYYYMM) returns the current month, [YYYYMM] will give an error as there is no row context .. you can adjust it so that this logic works, I think is just a bit more complex
Thanks for sharing please stay tuned for the official release and much more. We are just getting started!
Looking forward to see what's coming! 😀
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?
would work I think as long as you don't have a hierarchy (year , month) on rows, but just the concatenated yyyymm
Thank you for this great video !
Is there a way to use offset to return a text value instead of a numeric ??
Hey bro, for the multiple fields part, can we simply include the desired field in relation and orderby arguments instead of partition?
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.
Good function, but don't we lose possibility for customer to sort any column him/herself?
another masterpiece from BAS, thank you for sharing
thanks Santhosh! 😊
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.
It's a super cool feature. Great explanation Bas!
thx Raghavan
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?
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!
thanks for watching Ernest! 😊 my hope was that it would be similar to the lookup function in tableau. At the moment it is not quite there yet though as you miss the flexibility of the table calculations that tableau has (adjusting how you compute + ability to use measures for the sort)
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?
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.
great job, thanks very much! I appreciate the effort with the work arounds and also the candid assessment of it's usefulness.
Can any tell me, what is the use of this offset function in real time. Thanks
Absolutely amazing Mate 😄
thank you Norman!! 😊🙂
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!
Excellent, this is something I need. Can it be used in a calculated column on a table, I wonder? That'll make my life a lot easier if so.
Yes you can
It can be used in a calculated column, but I don't think it can be used to reference previous values of that same calculated column. So it won't help produce a running / cumulative total
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
Very clear explain, Thank you so much for your shown.👍👍
Well explained Bas, the OFFSET is not an easy function in Excel. For time intelligence: would not DATEADD be a lot more easier? I think you get the same result. As for the manufacturers example, I do not see (yet?) a practical business use.
thanks Bart! 🙂 definitely would stick to using dateadd for now and I'm also struggling to find good use cases ( that's also my conlusion in the video ;) ) . If you could use measures for the orderby function that would make it very useful though. Below in the comments a suggested use case is pareto analysis (didnt try it yet).
You made my day, thank you boss
💻💪🎉
Will this offset function work if for example do something like CurrentColumn = var previousevalue = Calculate([CurrentColumn], Offset(-1)).
should make calculating moving ranges a lot easier, so for XMR charts will help a lot!
Can this happened with week as well?
I still used to think, what makes Microsoft so complicated for not allowing us to use direct column reference in measures and values section, which will makes dax super easier.
Yeah but it will slow power bi too😅
Finally! Use this kind of analysis in M for big data is terrible.
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
Can’t you solve easily by doing -1 on a column that is year *100 + month and not show that column?
For a time based calculation, yes but for the second example (not time intelligence) you couldn't that.
@@dutchdatadude yes, I actually posted the comment in the middle of the video before resuming play ^^’
This does not work for multiple group bys
Great presentation
TOP! #powerbinareal
To do it over a longer period you could combine Year & Month in one column. "YYYY-MM"
Great Vedio 👌
thank you! 😊
I can't understand how it works for manufacturer column and not for month as both are text
it works for both .. it is just that the month column has a "sort by" column and therefore I had to include the monthnr (the column that is use for the sortby)
4:00- the arguments
Thanks. 🍓
🔥🔥🔥🔥
The Orderby now works on Total Sales :
OFFSET Manufacturer = CALCULATE(
[Total Sales],
OFFSET(
-1,
ALLSELECTED(dimProduct[Manufacturer]),
ORDERBY([Total Sales], ASC),
)
)
So the last part of the video is obsolete.
Fantastic, thanks
The OFFSET function will likely be a solid candidate for calculating delta values in previous rows, like we would in SQL with LAG(). If you want to see a demonstration of the similarities, here’s my latest video on the functionality:
ruclips.net/video/QYgi9Epzwsc/видео.html
Here is a possible practical use - offsetting weeks, since there's no native time intelligence for that.
I was thinking about that one as well, but the thing is that you would still need an weekid field (that would continue in the second year with week 53, 54, 55 etc) and offset it. This you could also do without it and using the offset function would be worse when you have a hierarchy (year and week on axis) as the first value would be blank
Great
Nice
Thanks 😊!
❤
Could be an easier solution for running totals, running averages
I clicked because I thought this was about Cardi B's husband, Offset.
🤣bit different
Already saw this….
Ok..👌
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
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🧐🧐🥸
This doesn't work for multiple groupings