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
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 :)
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!!
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
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.
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....
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.
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.
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
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.
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 :(
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
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?
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?
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?
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
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?
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
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!
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).
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/
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!
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 .. 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 😕😕
what about this formula? Would it work the same way? VAR NumOfMonth = 12 VAR Period = DATESINPERIOD ( 'Date'[Date], LASTDATE(dDate[Date]), -NumOfMonth, MONTH ) VAR Result = AVERAGEX ( Period,[Revenue] ) VAR LastDateWithSale = MAX ( SalesOrder Date] ) VAR FirstVisibleDate = MIN ( 'Date'[Date] ) RETURN IF ( FirstVisibleDate
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?
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).
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)?
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?
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/
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?
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
![ง้อ (ALRIGHT) - FOURTH Prod. by URBOYTJ [ OFFICIAL MV ]](http://i.ytimg.com/vi/Qs8ZeV6Dqj8/mqdefault.jpg)
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!
I like the way he explains. It is clear and soothing.
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.
You make DAX a work of art!
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!!
This is finally a working manual for rolling average. Perfect job!
I wish TH-cam had a LOVE button! THANK YOU FOR THIS!!!!!
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
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.
the best yt channel about dax
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
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!
Matero, Salute to you for your art of teaching DAX
Always the best tutorial I could ask to solve my problem. 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 () !!
Thank you so much! I could finish a presentation at work because of your help and expertise. Thank you!
Thanks as always for great content; the high quality explanation and precise communication of the thought process is what I find most powerful.
Amazing! You are the best teacher and a DAX genius! Thank you! ❤
Thank you for this. Very helpful and I love the way you explain how it works.
Thank you for this amazing content!! This empowers me to provide 10x more value to the company that I work for!
Was waiting for this. Heartly thankful to sir..from India 🙏
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....
every video takes big pleasure.After reading your book Thanks you 👍👍👍👍👍👍
Thank you alberto, this is really helpful and very well explained 🙏👏
Amazing video, struggled with this for so long!
This is awesome very clearly presentation and explanation
Excellent, as always. Thanks Alberto!
brilliantly explained
Thank you so much for this video, it was very clear and super helpful! I very happily subscribed to your channel! :D
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/
hey, I really like your videos. very useful, thanks for sharing 🙂
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.
Perfect, I was just working on this
Quality > Explanation > Clarity > Focus = SQLBI
Simple and elegant. Thanks!
Thank you Alberto
Very excellent and the explanation is wonderful
You are simply brrrrrrrrriliant!!!
great video, Dax ENJOYED!
Thanks one more time..! very useful and applicable
Excellent explanation
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?
Great video thanks!
Love this channel 🎉😂
Thank you Alberto!
Awesome. What if I have a help desk data and instead of sales have to count the tickets per month and them per year?
Its really helpful. Thanks for the help.
Great content as usual. Thank you for your hard work!
Thanks Alberto for great video.
Does Averagex works in Aggregated tables
I have a 300 Million Fact table. So I am using aggs
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.
Great tip! Thanks a lot !!!
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
Nice Explanation...
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.
Great teacher...
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
Great video Course!!!
Thank you this helps lot
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?
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
@SQL_BI, I wonder how to calculate rolling averages over hours?
The best!
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.
what needs to change in the formula if the same measure is to be used for computing rolling average for departments?
Is there anyway to calculate moving average on Non-Dates values?
Superb 👍 Thank you.
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!
how can i follow u if u dont show the data u are using and the measures previusly created...
Very nice!!
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?
Hey what If we use Datesinperiod inside calculate only ?
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
I have a question... If we use N parameters to get N rolling month, will it be less effecient than creating calculation groups?
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?
Fantastic 🔥🔥🔥
Who knew Frank from Father of the Bride was such a PBI monster?!
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
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/
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).
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!
What happens if you have a date table with date hierarchy? i can't make it work with it
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!
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 .. 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!)
what about this formula? Would it work the same way?
VAR NumOfMonth = 12
VAR Period = DATESINPERIOD ( 'Date'[Date], LASTDATE(dDate[Date]), -NumOfMonth, MONTH )
VAR Result =
AVERAGEX ( Period,[Revenue]
)
VAR LastDateWithSale = MAX ( SalesOrder Date] )
VAR FirstVisibleDate = MIN ( 'Date'[Date] )
RETURN
IF ( FirstVisibleDate
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?
How to caluculate previous year rolling averages
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).
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
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?
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/
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?
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
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
Tu sei un grande Alberto! #madeinitaly