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
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!!
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!
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 :)
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.
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 :(
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.
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?
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 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?
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).
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
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/
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
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
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?
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).
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 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!
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)?
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?
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/
This method no longer work due to filter context will pass through all element in data set. I do not know why ? Maybe DAX engine change policy By the way just use =Caculate([SaleAmonunt], Filter( All(Date) , Date[MonthNum] >= CurrentMonth -12 && Date[MonthNum]
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?
Great explanation Alberto, just one question. You can make this measure dynamic by using parameters, instead of hardcoding NumofMonths?? Thanks in advance.
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?
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?
I think there is a problem. If the measure has no value for a specific month then average formula ignores that month, but it have to be calculated as a ZERO. So i revised the averageX formula as; values(.... 0+[Sales Amount] ....
As usual, it depends on the requirements. If no data means 0, your approach is correct. If no data means that business was closed, you might want to ignore it. The average in DAX usually works like the latter, so we kept the same approach, but your approach correctly implements the former requirement.
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!
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!
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!
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.
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.
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!
This is finally a working manual for rolling average. Perfect job!
This is so helpful! Alberto always has the best in-depth analyses. Just an interesting observation: the FirstSelectedDay
Thank you so much. This is the best video I have seen on Running averages.
I wish RUclips had a LOVE button! THANK YOU FOR THIS!!!!!
Always the best tutorial I could ask to solve my problem. Thank you 🙏🏻
the best yt channel about dax
Thank you so much! I could finish a presentation at work because of your help and expertise. Thank you!
Matero, Salute to you for your art of teaching DAX
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.
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.
Quality > Explanation > Clarity > Focus = SQLBI
every video takes big pleasure.After reading your book Thanks you 👍👍👍👍👍👍
Thank you alberto, this is really helpful and very well explained 🙏👏
brilliantly explained
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/
Thank you for this amazing content!! This empowers me to provide 10x more value to the company that I work for!
You are simply brrrrrrrrriliant!!!
This is awesome very clearly presentation and explanation
Amazing video, struggled with this for so long!
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....
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.
hey, I really like your videos. very useful, thanks for sharing 🙂
Excellent, as always. Thanks Alberto!
Thank you so much for this video, it was very clear and super helpful! I very happily subscribed to your channel! :D
Perfect, I was just working on this
Simple and elegant. Thanks!
Thank you Alberto
Love this channel 🎉😂
Very excellent and the explanation is wonderful
great video, Dax ENJOYED!
Thanks one more time..! very useful and applicable
Great video thanks!
Its really helpful. Thanks for the help.
Excellent explanation
Great content as usual. Thank you for your hard work!
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?
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?
Thanks Alberto for great video.
Does Averagex works in Aggregated tables
I have a 300 Million Fact table. So I am using aggs
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 tip! Thanks a lot !!!
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.
Nice Explanation...
Great video Course!!!
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
Thank you this helps lot
Who knew Frank from Father of the Bride was such a PBI monster?!
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.
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.
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?
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 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!
Fantastic 🔥🔥🔥
Very nice!!
Superb 👍 Thank you.
what needs to change in the formula if the same measure is to be used for computing rolling average for departments?
The best!
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).
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
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!
how can i follow u if u dont show the data u are using and the measures previusly created...
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
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
Is there anyway to calculate moving average on Non-Dates values?
What happens if you have a date table with date hierarchy? i can't make it work with it
Tu sei un grande Alberto! #madeinitaly
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?
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).
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 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!
Hey what If we use Datesinperiod inside calculate only ?
How to caluculate previous year rolling averages
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 have a question... If we use N parameters to get N rolling month, will it be less effecient than creating calculation groups?
@SQL_BI, I wonder how to calculate rolling averages over hours?
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?
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/
This method no longer work due to filter context will pass through all element in data set. I do not know why ? Maybe DAX engine change policy
By the way just use =Caculate([SaleAmonunt],
Filter(
All(Date) ,
Date[MonthNum] >= CurrentMonth -12 &&
Date[MonthNum]
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?
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
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?
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?
I think there is a problem. If the measure has no value for a specific month then average formula ignores that month, but it have to be calculated as a ZERO. So i revised the averageX formula as;
values(....
0+[Sales Amount]
....
As usual, it depends on the requirements. If no data means 0, your approach is correct. If no data means that business was closed, you might want to ignore it. The average in DAX usually works like the latter, so we kept the same approach, but your approach correctly implements the former requirement.
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!)