Computing rolling average in DAX
Vložit
- čas přidán 2. 08. 2024
- Computing the rolling 12-months average in DAX looks simple, but it hides some level of complexity. This article explains how to write the best formula and how to avoid common pitfalls using time intelligence functions.
Article and download: sql.bi/15161?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin... - Věda a technologie
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!
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.
Thanks as always for great content; the high quality explanation and precise communication of the thought process is what I find most powerful.
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!
Amazing! You handle DAX like Einstein used to handle phisics.
Congrats Alberto! Thanks for sharing knowledge.
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 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 finally a working manual for rolling average. Perfect job!
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....
Thank you so much. This is the best video I have seen on Running averages.
Always the best tutorial I could ask to solve my problem. Thank you 🙏🏻
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 for this amazing content!! This empowers me to provide 10x more value to the company that I work for!
Thank you for this. Very helpful and I love the way you explain how it works.
Excellent, as always. Thanks Alberto!
Amazing video, struggled with this for so long!
Real mastery indeed. Wow!! Love the elegant solution!! Thank you for the awesome () !!
Great content as usual. Thank you for your hard work!
Matero, Salute to you for your art of teaching DAX
I wish CZcams had a LOVE button! THANK YOU FOR THIS!!!!!
Amazing! You are the best teacher and a DAX genius! 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!
Simple and elegant. Thanks!
Was waiting for this. Heartly thankful to sir..from India 🙏
Thank you so much for this video, it was very clear and super helpful! I very happily subscribed to your channel! :D
This is awesome very clearly presentation and explanation
Thank you alberto, this is really helpful and very well explained 🙏👏
every video takes big pleasure.After reading your book Thanks you 👍👍👍👍👍👍
Thank you Alberto!
Great tip! Thanks a lot !!!
Perfect, I was just working on this
Very excellent and the explanation is wonderful
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/
Thanks one more time..! very useful and applicable
great video, Dax ENJOYED!
brilliantly explained
hey, I really like your videos. very useful, thanks for sharing 🙂
You are simply brrrrrrrrriliant!!!
Great video thanks!
Thank you Alberto
Excellent explanation
Great video Course!!!
Its really helpful. Thanks for the help.
Great teacher...
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.
Nice Explanation...
Thank you this helps lot
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?
Love this channel 🎉😂
Superb 👍 Thank you.
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
Very nice!!
Fantastic 🔥🔥🔥
Bravo 👏
Quality > Explanation > Clarity > Focus = SQLBI
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?
The best!
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/
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?
Thanks Alberto for great video.
Does Averagex works in Aggregated tables
I have a 300 Million Fact table. So I am using aggs
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?
Tu sei un grande Alberto! #madeinitaly
Awesome. What if I have a help desk data and instead of sales have to count the tickets per month and them per year?
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 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 ! 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
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.
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?
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/
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!
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/
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.
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/
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
Amazing! Can this be done with Power Pivot as well?
Yes!
Hey what If we use Datesinperiod inside calculate only ?
I have a question... If we use N parameters to get N rolling month, will it be less effecient than creating calculation groups?
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?
@SQL_BI, I wonder how to calculate rolling averages over hours?
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
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
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?
Who knew Frank from Father of the Bride was such a PBI monster?!
How to caluculate previous year rolling averages
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 do this for last 12 weeks ?
What happens if you have a date table with date hierarchy? i can't make it work with it
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
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!)
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/
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