I wish I could understand DAX in such a fluent manner, to be able to quickly throw that type of measure/calculated group together. Pure Joy & art to see someone who is fluent create such a powerful visual
Sir, I would like to thank you so utterly from the bottom of my heart. I've been unable to use 12M rolling avg in my powerbi as something funky always happened in the background that left me with wonky values.. I've been searching for days, have watched countless videos and help threads and nothing worked. That is, until I came across your fabulous video. It finally worked! Thank you so much!
This has been the latest requirement in my organization and I am just getting to creating this calculation group. You just brought sped up my implementation time. Super greatful!!
Nobody teaches like you.. you explained the logic, thereafter you applied in the blank model. Even we can't expect this much of explanation in paid courses. Thank for you sharing your knowledge :)
Excellent - love how you made the concept easy to understand. What I really like is you explained it with a big picture in mind, and very logical. Thank you for sharing.
Hi Alberto, You are really amazing & Rockstar in DAX after watch your Power BI tutorial I realize DAX is very easy. your way of explanation is very very simple and clear . Thanks you have been created these very important tutorial all of us. Thanks
Thank you Alberto!! This helped me solve my issue that I have been putting off for weeks. Of course you take it to another level completely by adding a Calculation group! Great work. Now to just get Tabular Editor on our approved software so I can dig a little deeper....
can you achieve a 3 months rolling average as a calculated column, most examples i have seen is that of calculated measure, would like a demo of the same feature as a calculated column.
Hello my friend. I am Angel Vargas. I am from Chile. How can we develop and implement in DAX language the Holt Winter model to project demand, sales and business contribution? Thanks.
At 9:15, I actually want the measure to consider the "empty" data. In my data, I have months with data, and months without. But I'm facing empty months with the same average of the previous month. What I'm missing? Example: Jan/20: sales = 4, 12m avg = 4 Feb/20: sales = 1, 12m avg = 2.5 Mar/20: sales = empty, 12m avg = 2.5 (I wish it was 1.66, i.e. considering the empty value as 0). Any guidance? Ciao!
I did a few tweaks on my own that may help someone: 1. Changed my "sales" metric, adding an IF statement: IF ( ISBLANK(Sales), 0, Sales). It worked to fill every month with blank data in the middle of the data. However, this won't allow me to get rid of the blank data at the end of the data. 2. Adjusted my date table references My date table was considering 3 fact tables and one of them (the least relevant) considered a longer period. I removed it from the reference, so my date table now considers the period I really want to consider (2020-Present).
Thank you for the video. I do have a question though, what is the different between 'Date' [Date] and 'Date[Calender Year Month Number] column? Unfortuantely, I cannot see the columns in the Date table and what they imply. Thank you for your prompt.
@@SQLBI Hi Alberto, many thanks for your reply. The thing is that I am using the DatesInPeriod function for var Period = DATESINPERIOD(Sheet1[rep_date_w_LateB], LASTDATE( Sheet1[rep_date_w_LateB] ),-3,MONTH) however, I receive an error: "A table of multiple values was supplied where a single value is expected." Can you please advise what I am doing wrong?
Hi Alberto, Thanks for all you do for the Power BI community. I have created a Power BI report that uses field parameters in a chart to allow user to select Vol, Sales and Cost. I also created simple moving average, but business wants to include only days where there are sales. I.e. Non-contiguous dates. For the moving average, I am also using parameter to allow user to select from 5 to 100 days moving average. I can do a simple moving average but having challenges with non-contiguous dates. Any tips or assistance you can provide would be greatly appreciated. Thanks
In my case it worked only after having done some additions to the formula. When I applied the formula you explained I got nothing different than each month's total individually. So I added another variable var DatesInRange = FILTER(ALL(DimDate), Date[Date] IN Period), and swapped it with 'Period' in 'Result'. So I could get the desired moving monthly average over past 12 moths. But thanks for the inspiration and fabulous explanation.
Ciao Alberto, great video as always. I actually read the original/previous article about Rolling AVG before. I also used it as a starting point for my measure. However, I would like to ask your opinion about something. I would like to show rolling 3 months only from the 4th months in my model. Same with the rolling 6 months, I only want to see the line from the 7th month in my model. *this is also a business requirement. What sort of a best practice would you suggest when it comes to optimizing my DAX? Grazie tanto mille!
Thank you ! This really help me on my Work! BUT I have only 1 question please!! how to calculate the total of the Rolling average sum ? I tried with an If, HASONEVALUE and SUMX but it does not work :(
Nice! I always doubt if I need a RemoveFilters('Date') inside calculate. Would it work if year month was not on the visual and it was over year week (for example)?
This has been so helpful! I do have a question though. What if your "Sales Amount" column had zeros? How would you calculate rolling averages then? I have my data setup just like your example, but for the measures that calculate averages, they are not showing any results if that month's sales amount is zero. Any suggestions?
At 7:58, the first year starts with the month number, 31, and increased incremental. But why next years started 365, if the number reset, why not started again the days of month?
The former gets an entire period starting from a single reference date, the latter shift the list of dates provided by the offset requested. Look at examples (and videos) on DAX.guide: dax.guide/datesinperiod/ dax.guide/dateadd/
In the Sales R12M calculation, I get a result in the totals row, which repeats the last calculated value. How did you hide the Totals result for this column?
Excellent !!😊, just want to know, if some months data are missing in 12M rolling then how we can do the calculations. I mean , suppose for Nov-21, we need the values from Dec-20 to Nov-21 but suppose Jan-21/Feb-21/Mar-31 data is not available then how we can adjust the measure?
Hi Sir, can we do rolling with adding any dimension column as a legend? For example here adding Product in legend and distibuting the rolling average for individual product?
A problem that I have been working on is calculating a rolling average at a product code granularity that is based on the past 10 business days that does not factor in 0 or blank sales on Saturdays or Sundays but does factor in 0 or blank sales during a weekday. Any thoughts on modifying the code shown in the video?
Hi Why its doing average for year only. Values function returns all distinct year moths Sales amount and finally does arithmetic mean . The final calculation is for all years average amount. When the context is day wise or month wise its only showing average for year only. Please assist
Great explanation Alberto, just one question. You can make this measure dynamic by using parameters, instead of hardcoding NumofMonths?? Thanks in advance.
Does Microsoft have any plans to enable global parameters in PowerBI, so parameters on one worksheet can affect measures on another worksheet? Currently they only work if the visual is on the same sheet as the parameter. Thanks for the great content!
You can already synchronize a slicer across several pages, but you cannot affect external PBIX files. See documentation: docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers#sync-and-use-slicers-on-other-pages
3 ปีที่แล้ว +1
Hi Alberto. Link (Article and download) is broken, it's a 404 error Thank You for sharing
Amazing, I was just working on this. Have couple of questions: 1) How will the measure vary if we have a slicer selection suppose of customer ? 2) We have a page level filter of suppose last 2 year of data and the measure should be filtered by this page level filter?
1) It just uses those customers. 2) This could be more challenging because you don't want a cumulative total but more an "extended" year-to-date. You should modify a year-to-date calculation, getting the right starting date (ALLSELECTED could help). You can find custom time intelligence calculations here: www.daxpatterns.com/custom-time-related-calculations/
I've used this method successfully, however one request I've had is the ability to add a drill through to see the values rolled up in a particular point. Is there a way with DAX to modify the filter context on a drill through so that this could be achieved? Maybe a good topic for a follow up video?
You should implement the same logic in the Detail Rows expression - but it can be consumed only by Excel: www.sqlbi.com/articles/controlling-drillthrough-using-detail-rows-expressions-in-dax/
I did a 7 day version of this and when I enter this DAX code it stops calculating the rolling average after there are no records. For example, if there were no sales on 9/9 and forward you would think the running average would continue for 6 more days but the running average defaults to zero from 9/9 and forward. Anyone had this issue?
Trying to calculate the 12 month rolling average for a rate (Measure). It doesn't seem to be working and is just returning the same value as the current month's value (not the average of the last 12 months).
I am using the quickmeasure for the rolling average but cannot figure out how to get the row-by-row average you have there. Only getting the total average. Agh!
It's and amazing video, My requirement is that the calculation shouldn't happen for first 5 months. Ex: sales starting from Jan 2007 So I don't want the average to be calculated for Jan-May 2007, and average to start from the month of June 2007. How I'll be able to achieve this. Any solution will be very helpful Thanks
Hello, thanks for sharing, very helpful. I also need help, I want to create a moving average for 4 quarters, but start from the previous quarter, not from the last quarter. For example, for the 4th quarter of this year, I need to calculate the 4th quarter of last year and the 1st, 2nd and 3rd quarters of this year. Thank you in advance for your help
Could you please create a video where you can explain how to manipulate the date table, like how you created CY 2007 column and how you arranged the month in monthly order, when I try to replicate the same it's getting arranged in alphabetical order Please help me
Thank you .. pitifully where I work, we have no access to external tools such as Tableau Editor like you do .. and as a result I can't create the calculation group 😕😕
I wish I could understand DAX in such a fluent manner, to be able to quickly throw that type of measure/calculated group together. Pure Joy & art to see someone who is fluent create such a powerful visual
Alberto and Marco - your videos fill me with pure unadulterated joy!
Sir, I would like to thank you so utterly from the bottom of my heart. I've been unable to use 12M rolling avg in my powerbi as something funky always happened in the background that left me with wonky values.. I've been searching for days, have watched countless videos and help threads and nothing worked. That is, until I came across your fabulous video. It finally worked! Thank you so much!
I like the way he explains. It is clear and soothing.
This has been the latest requirement in my organization and I am just getting to creating this calculation group. You just brought sped up my implementation time. Super greatful!!
You make DAX a work of art!
Nobody teaches like you.. you explained the logic, thereafter you applied in the blank model. Even we can't expect this much of explanation in paid courses. Thank for you sharing your knowledge :)
Amazing! You handle DAX like Einstein used to handle phisics.
Congrats Alberto! Thanks for sharing knowledge.
I wish TH-cam had a LOVE button! THANK YOU FOR THIS!!!!!
This is finally a working manual for rolling average. Perfect job!
the best yt channel about dax
Excellent - love how you made the concept easy to understand. What I really like is you explained it with a big picture in mind, and very logical. Thank you for sharing.
Matero, Salute to you for your art of teaching DAX
Hi Alberto,
You are really amazing & Rockstar in DAX
after watch your Power BI tutorial I realize DAX is very easy.
your way of explanation is very very simple and clear .
Thanks you have been created these very important tutorial all of us.
Thanks
This is a good case of use and a practical way to use all the flexibility of calc groups of DAX. Thanks to share with us!
This is so helpful! Alberto always has the best in-depth analyses. Just an interesting observation: the FirstSelectedDay
You are always the number one! I find always a solution to my Power BI problems in your videos! and very clear. Thank you
Thank you so much. This is the best video I have seen on Running averages.
Real mastery indeed. Wow!! Love the elegant solution!! Thank you for the awesome () !!
Thanks as always for great content; the high quality explanation and precise communication of the thought process is what I find most powerful.
Thank you so much! I could finish a presentation at work because of your help and expertise. Thank you!
Always the best tutorial I could ask to solve my problem. Thank you 🙏🏻
This is awesome very clearly presentation and explanation
every video takes big pleasure.After reading your book Thanks you 👍👍👍👍👍👍
Amazing video, struggled with this for so long!
Excellent, as always. Thanks Alberto!
Thank you for this. Very helpful and I love the way you explain how it works.
Thank you Alberto!! This helped me solve my issue that I have been putting off for weeks. Of course you take it to another level completely by adding a Calculation group! Great work. Now to just get Tabular Editor on our approved software so I can dig a little deeper....
Amazing! You are the best teacher and a DAX genius! Thank you! ❤
Was waiting for this. Heartly thankful to sir..from India 🙏
Thank you for this amazing content!! This empowers me to provide 10x more value to the company that I work for!
Awesome. What if I have a help desk data and instead of sales have to count the tickets per month and them per year?
Thank you alberto, this is really helpful and very well explained 🙏👏
Thanks Alberto for great video.
Does Averagex works in Aggregated tables
I have a 300 Million Fact table. So I am using aggs
Simple and elegant. Thanks!
Amazing content! Just 1 ques if there is a month slicer, how do I exclude the month slicer and still showing rolling average of 12 month?
Excellent video, many thanks. Would love to see a similar video showing best practice for sales comparison to previous periods - year, qtr, month etc.
Watch articles and videos in DAX Patterns - Time Intelligence patterns cover all these calculations: www.daxpatterns.com/time-patterns/
Very excellent and the explanation is wonderful
can you achieve a 3 months rolling average as a calculated column, most examples i have seen is that of calculated measure, would like a demo of the same feature as a calculated column.
Hello my friend. I am Angel Vargas. I am from Chile. How can we develop and implement in DAX language the Holt Winter model to project demand, sales and business contribution? Thanks.
brilliantly explained
At 9:15, I actually want the measure to consider the "empty" data. In my data, I have months with data, and months without. But I'm facing empty months with the same average of the previous month. What I'm missing?
Example:
Jan/20: sales = 4, 12m avg = 4
Feb/20: sales = 1, 12m avg = 2.5
Mar/20: sales = empty, 12m avg = 2.5 (I wish it was 1.66, i.e. considering the empty value as 0).
Any guidance?
Ciao!
I did a few tweaks on my own that may help someone:
1. Changed my "sales" metric, adding an IF statement: IF ( ISBLANK(Sales), 0, Sales).
It worked to fill every month with blank data in the middle of the data. However, this won't allow me to get rid of the blank data at the end of the data.
2. Adjusted my date table references
My date table was considering 3 fact tables and one of them (the least relevant) considered a longer period. I removed it from the reference, so my date table now considers the period I really want to consider (2020-Present).
Thank you for the video. I do have a question though, what is the different between 'Date' [Date] and 'Date[Calender Year Month Number] column? Unfortuantely, I cannot see the columns in the Date table and what they imply. Thank you for your prompt.
Calendar Year Month Number is a sequential number for every month, whereas Date is the simply the date of the row.
@@SQLBI Hi Alberto, many thanks for your reply. The thing is that I am using the DatesInPeriod function for var Period = DATESINPERIOD(Sheet1[rep_date_w_LateB], LASTDATE( Sheet1[rep_date_w_LateB] ),-3,MONTH) however, I receive an error: "A table of multiple values was supplied where a single value is expected." Can you please advise what I am doing wrong?
Hi Alberto, actually, I realized it did not work because I returned not the count of rows :). Thank you for your help!
Perfect, I was just working on this
Hi Alberto, Thanks for all you do for the Power BI community. I have created a Power BI report that uses field parameters in a chart to allow user to select Vol, Sales and Cost. I also created simple moving average, but business wants to include only days where there are sales. I.e. Non-contiguous dates.
For the moving average, I am also using parameter to allow user to select from 5 to 100 days moving average. I can do a simple moving average but having challenges with non-contiguous dates.
Any tips or assistance you can provide would be greatly appreciated. Thanks
In my case it worked only after having done some additions to the formula. When I applied the formula you explained I got nothing different than each month's total individually. So I added another variable
var DatesInRange = FILTER(ALL(DimDate), Date[Date] IN Period), and swapped it with 'Period' in 'Result'. So I could get the desired moving monthly average over past 12 moths. But thanks for the inspiration and fabulous explanation.
Thank you so much for this video, it was very clear and super helpful! I very happily subscribed to your channel! :D
Excellent explanation
You are simply brrrrrrrrriliant!!!
great video, Dax ENJOYED!
@SQL_BI, I wonder how to calculate rolling averages over hours?
Ciao Alberto, great video as always. I actually read the original/previous article about Rolling AVG before. I also used it as a starting point for my measure. However, I would like to ask your opinion about something. I would like to show rolling 3 months only from the 4th months in my model. Same with the rolling 6 months, I only want to see the line from the 7th month in my model. *this is also a business requirement. What sort of a best practice would you suggest when it comes to optimizing my DAX? Grazie tanto mille!
See the more complete measures in DAX Patterns - Time Intelligence.
For example: www.daxpatterns.com/standard-time-related-calculations/
Thank you ! This really help me on my Work!
BUT I have only 1 question please!! how to calculate the total of the Rolling average sum ?
I tried with an If, HASONEVALUE and SUMX but it does not work :(
Hi ,have you got any solution for this . Iam also facing same issue
Nice! I always doubt if I need a RemoveFilters('Date') inside calculate. Would it work if year month was not on the visual and it was over year week (for example)?
REMOVEFILTERS( Date ) has just that purpose - keep it and it will work also with year week.
@@SQLBI so the code shown would need a REMOVEFILTERS('Date') to work in any visual that doesn't use year week as dimension
This has been so helpful! I do have a question though. What if your "Sales Amount" column had zeros? How would you calculate rolling averages then? I have my data setup just like your example, but for the measures that calculate averages, they are not showing any results if that month's sales amount is zero. Any suggestions?
Blank are ignored, 0 are not ignored - if you want to ignore them, filter those rows out or modify the measure so that it returns BLANK instead of 0.
At 7:58, the first year starts with the month number, 31, and increased incremental. But why next years started 365, if the number reset, why not started again the days of month?
Thanks one more time..! very useful and applicable
I am able to use this in visuals but in tables Totals are missing for this measure. How could I get it to count also totals in table?
hey, I really like your videos. very useful, thanks for sharing 🙂
Its really helpful. Thanks for the help.
how can i follow u if u dont show the data u are using and the measures previusly created...
Thanks for the video! Clearly explained.
May I know the difference between DATEINPERIOD and DATEADD? Appreciate your advice.
The former gets an entire period starting from a single reference date, the latter shift the list of dates provided by the offset requested.
Look at examples (and videos) on DAX.guide:
dax.guide/datesinperiod/
dax.guide/dateadd/
@@SQLBIthank you so much!
Quality > Explanation > Clarity > Focus = SQLBI
In the Sales R12M calculation, I get a result in the totals row, which repeats the last calculated value. How did you hide the Totals result for this column?
Hey what If we use Datesinperiod inside calculate only ?
Great content as usual. Thank you for your hard work!
Is there anyway to calculate moving average on Non-Dates values?
Nice Explanation...
Excellent !!😊, just want to know, if some months data are missing in 12M rolling then how we can do the calculations. I mean , suppose for Nov-21, we need the values from Dec-20 to Nov-21 but suppose Jan-21/Feb-21/Mar-31 data is not available then how we can adjust the measure?
Hi Sir, can we do rolling with adding any dimension column as a legend? For example here adding Product in legend and distibuting the rolling average for individual product?
Thank you Alberto!
what needs to change in the formula if the same measure is to be used for computing rolling average for departments?
I have a question... If we use N parameters to get N rolling month, will it be less effecient than creating calculation groups?
What happens if you have a date table with date hierarchy? i can't make it work with it
Great video Course!!!
A problem that I have been working on is calculating a rolling average at a product code granularity that is based on the past 10 business days that does not factor in 0 or blank sales on Saturdays or Sundays but does factor in 0 or blank sales during a weekday. Any thoughts on modifying the code shown in the video?
Amazing! Can this be done with Power Pivot as well?
Yes!
Hi
Why its doing average for year only. Values function returns all distinct year moths Sales amount and finally does arithmetic mean . The final calculation is for all years average amount. When the context is day wise or month wise its only showing average for year only. Please assist
Do you have any solutions if the date presented not by date standart but just numbers of weeks?
Check more complete patterns like www.daxpatterns.com/week-related-calculations/ (look for moving average)
Great explanation Alberto, just one question. You can make this measure dynamic by using parameters, instead of hardcoding NumofMonths?? Thanks in advance.
Yes, you can use a slicer and retrieve that selection by using the measure created by the wizard parameter, or by using SELECTEDVALUE
Thank you Alberto
Does Microsoft have any plans to enable global parameters in PowerBI, so parameters on one worksheet can affect measures on another worksheet? Currently they only work if the visual is on the same sheet as the parameter. Thanks for the great content!
You can already synchronize a slicer across several pages, but you cannot affect external PBIX files.
See documentation: docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers#sync-and-use-slicers-on-other-pages
Hi Alberto.
Link (Article and download) is broken, it's a 404 error
Thank You for sharing
It's fixed now - thanks for reporting it!
Great video thanks!
Great teacher...
How can I do this for last 12 weeks ?
Thanks! Do you have a tutorial to integrate Tabular Editor to Power BI?
Not yet, but you can find links to other video tutorials in the Tabular Editor documentation: docs.tabulareditor.com/
Great tip! Thanks a lot !!!
Amazing, I was just working on this. Have couple of questions:
1) How will the measure vary if we have a slicer selection suppose of customer ?
2) We have a page level filter of suppose last 2 year of data and the measure should be filtered by this page level filter?
1) It just uses those customers.
2) This could be more challenging because you don't want a cumulative total but more an "extended" year-to-date. You should modify a year-to-date calculation, getting the right starting date (ALLSELECTED could help). You can find custom time intelligence calculations here: www.daxpatterns.com/custom-time-related-calculations/
I've used this method successfully, however one request I've had is the ability to add a drill through to see the values rolled up in a particular point. Is there a way with DAX to modify the filter context on a drill through so that this could be achieved? Maybe a good topic for a follow up video?
You should implement the same logic in the Detail Rows expression - but it can be consumed only by Excel:
www.sqlbi.com/articles/controlling-drillthrough-using-detail-rows-expressions-in-dax/
I did a 7 day version of this and when I enter this DAX code it stops calculating the rolling average after there are no records. For example, if there were no sales on 9/9 and forward you would think the running average would continue for 6 more days but the running average defaults to zero from 9/9 and forward. Anyone had this issue?
Trying to calculate the 12 month rolling average for a rate (Measure). It doesn't seem to be working and is just returning the same value as the current month's value (not the average of the last 12 months).
I am using the quickmeasure for the rolling average but cannot figure out how to get the row-by-row average you have there. Only getting the total average. Agh!
Love this channel 🎉😂
It's and amazing video,
My requirement is that the calculation shouldn't happen for first 5 months.
Ex: sales starting from Jan 2007
So I don't want the average to be calculated for Jan-May 2007, and average to start from the month of June 2007.
How I'll be able to achieve this.
Any solution will be very helpful
Thanks
Hello, thanks for sharing, very helpful.
I also need help, I want to create a moving average for 4 quarters, but start from the previous quarter, not from the last quarter.
For example, for the 4th quarter of this year, I need to calculate the 4th quarter of last year and the 1st, 2nd and 3rd quarters of this year.
Thank you in advance for your help
You can probably adapt one of the formulas from the Time Intelligence patterns: www.daxpatterns.com/time-patterns/
Could you please create a video where you can explain how to manipulate the date table, like how you created CY 2007 column and how you arranged the month in monthly order, when I try to replicate the same it's getting arranged in alphabetical order
Please help me
Superb 👍 Thank you.
Fantastic 🔥🔥🔥
Thank you .. pitifully where I work, we have no access to external tools such as Tableau Editor like you do .. and as a result I can't create the calculation group 😕😕
The feature should arrive in PBI Desktop soon (after 4 years!)