Computing rolling average in DAX

[@mathew9665]

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

[@mangeshmehendale4139]

Alberto and Marco - your videos fill me with pure unadulterated joy!

[@anson7140]

I like the way he explains. It is clear and soothing.

[@kaito4412]

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!

[@motichoorladdu]

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!!

[@vijayvizzu1]

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 :)

[@johan5315]

You make DAX a work of art!

[@roadtech6472]

Amazing! You handle DAX like Einstein used to handle phisics. Congrats Alberto! Thanks for sharing knowledge.

[@tiffanyharris711]

I wish KZbin had a LOVE button! THANK YOU FOR THIS!!!!!

[@raqzg2083]

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.

[@GrabowskiMaciej]

the best yt channel about dax

[@michaelazelena9060]

This is finally a working manual for rolling average. Perfect job!

[@ashishmohan4707]

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

[@cesarebellentani]

You are always the number one! I find always a solution to my Power BI problems in your videos! and very clear. Thank you

[@lbarbyery]

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!

[@mounjayansaha4750]

Thank you so much. This is the best video I have seen on Running averages.

[@Truth-N-Lies]

Matero, Salute to you for your art of teaching DAX

[@stevenknight5920]

Thanks as always for great content; the high quality explanation and precise communication of the thought process is what I find most powerful.

[@alohaaloha8437]

This is so helpful! Alberto always has the best in-depth analyses. Just an interesting observation: the FirstSelectedDay

[@andreeaolah6543]

Real mastery indeed. Wow!! Love the elegant solution!! Thank you for the awesome () !!

[@panyaphatthurapra7967]

Always the best tutorial I could ask to solve my problem. Thank you 🙏🏻

[@francismastro]

Thank you for this. Very helpful and I love the way you explain how it works.

[@ashleygesty7671]

Amazing video, struggled with this for so long!

[@emilyamalkin4367]

Amazing! You are the best teacher and a DAX genius! Thank you! ❤

[@alexandreluizrosa9933]

Thank you so much! I could finish a presentation at work because of your help and expertise. Thank you!

[@migmanc]

Excellent, as always. Thanks Alberto!

[@AccessAnalytic]

Excellent explanation

[@maybinkatongo2568]

This is awesome very clearly presentation and explanation

[@aaronroethe1010]

Thank you for this amazing content!! This empowers me to provide 10x more value to the company that I work for!

[@QuentinBeauquesne-uw8mh]

Thank you alberto, this is really helpful and very well explained 🙏👏

[@sbn4862]

every video takes big pleasure.After reading your book Thanks you 👍👍👍👍👍👍

[@ambarkdi9186]

Was waiting for this. Heartly thankful to sir..from India 🙏

[@melissabenito1376]

Thank you so much for this video, it was very clear and super helpful! I very happily subscribed to your channel! :D

[@TorbenSvensson]

brilliantly explained

[@tomm6269]

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....

[@arksector]

Simple and elegant. Thanks!

[@mohamedkhalid8117]

Very excellent and the explanation is wonderful

[@jaggyjut]

Awesome. What if I have a help desk data and instead of sales have to count the tickets per month and them per year?

[@Low-cd1zj]

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?

[@russskinner5715]

Excellent video, many thanks. Would love to see a similar video showing best practice for sales comparison to previous periods - year, qtr, month etc.

[@SQLBI]

Watch articles and videos in DAX Patterns - Time Intelligence patterns cover all these calculations: www.daxpatterns.com/time-patterns/

[@ivanak7149]

hey, I really like your videos. very useful, thanks for sharing 🙂

[@gambu4810]

You are simply brrrrrrrrriliant!!!

[@goldwhispers]

great video, Dax ENJOYED!

[@MrMalorian]

Perfect, I was just working on this

[@adeelyousafMughalsOnline]

Its really helpful. Thanks for the help.

[@reananand2025]

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.

[@tacijjola]

Thank you Alberto

[@julie_chen]

Love this channel 🎉😂

[@antoniogarrido3058]

Thanks one more time..! very useful and applicable

[@NoShadowOfDoubt1]

Thank you Alberto!

[@BIDataLab]

Great content as usual. Thank you for your hard work!

[@nikhil1888]

Thanks Alberto for great video. Does Averagex works in Aggregated tables I have a 300 Million Fact table. So I am using aggs

[@NataliyaIgnatova-kr3zp]

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]

Calendar Year Month Number is a sequential number for every month, whereas Date is the simply the date of the row.

[@NataliyaIgnatova-kr3zp]

@@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?

[@NataliyaIgnatova-kr3zp]

Hi Alberto, actually, I realized it did not work because I returned not the count of rows :). Thank you for your help!

[@matheusmascarenhas]

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!

[@matheusmascarenhas]

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).

[@3danim8r1]

Great teacher...

[@sravankumar1767]

Nice Explanation...

[@cristiannievesp]

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 :(

[@sahilkothekar9927]

Hi ,have you got any solution for this . Iam also facing same issue

[@Lhen134]

Great video thanks!

[@ThreadRider]

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

[@cezaryczajka9198]

Great tip! Thanks a lot !!!

[@sedasert7322]

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.

[@rolandszirmai3922]

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!

[@SQLBI]

See the more complete measures in DAX Patterns - Time Intelligence. For example: www.daxpatterns.com/standard-time-related-calculations/

[@angmatvargasbah]

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.

[@tatianamelnikova9780]

Great video Course!!!

[@manuprakash1989]

Fantastic 🔥🔥🔥

[@itsabigdiehl]

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?

[@SQLBI]

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.

[@lifeisgreat11]

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?

[@wmfexcel]

Thanks for the video! Clearly explained. May I know the difference between DATEINPERIOD and DATEADD? Appreciate your advice.

[@SQLBI]

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/

[@wmfexcel]

@@SQLBIthank you so much!

[@jansjoblom1602]

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?

[@robstewart8531]

Amazing! Can this be done with Power Pivot as well?

[@SQLBI]

Yes!

[@BernatAgulloRosello]

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)?

[@SQLBI]

REMOVEFILTERS( Date ) has just that purpose - keep it and it will work also with year week.

[@BernatAgulloRosello]

@@SQLBI so the code shown would need a REMOVEFILTERS('Date') to work in any visual that doesn't use year week as dimension

[@mnowako]

Superb 👍 Thank you.

[@impressive7920]

how can i follow u if u dont show the data u are using and the measures previusly created...

[@yasseryahya4094]

@SQL_BI, I wonder how to calculate rolling averages over hours?

[@thewarriargaming3663]

Thank you this helps lot

[@NimlaKotadia]

what needs to change in the formula if the same measure is to be used for computing rolling average for departments?

Hi Alberto. Link (Article and download) is broken, it's a 404 error Thank You for sharing

[@SQLBI]

It's fixed now - thanks for reporting it!

[@AUXA_2014]

Is there anyway to calculate moving average on Non-Dates values?

[@Ultramarplogic]

What happens if you have a date table with date hierarchy? i can't make it work with it

[@nareshpatel1035]

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?

[@irfandaud7380]

Quality > Explanation > Clarity > Focus = SQLBI

[@juanignacioXI]

Great explanation Alberto, just one question. You can make this measure dynamic by using parameters, instead of hardcoding NumofMonths?? Thanks in advance.

[@SQLBI]

Yes, you can use a slicer and retrieve that selection by using the measure created by the wizard parameter, or by using SELECTEDVALUE

[@jburris6507]

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?

[@danielfranchini6396]

Thanks! Do you have a tutorial to integrate Tabular Editor to Power BI?

[@SQLBI]

Not yet, but you can find links to other video tutorials in the Tabular Editor documentation: docs.tabulareditor.com/

[@AaronPaul_google]

How can I do this for last 12 weeks ?

[@SilvanoPaxia]

Tu sei un grande Alberto! #madeinitaly

[@jorparts]

Very nice!!

[@sujatarao9913]

Hey what If we use Datesinperiod inside calculate only ?

[@ChristopherMoore1913]

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!

[@SQLBI]

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

[@sahmedalee]

I have a question... If we use N parameters to get N rolling month, will it be less effecient than creating calculation groups?

[@allmazd]

Do you have any solutions if the date presented not by date standart but just numbers of weeks?

[@SQLBI]

Check more complete patterns like www.daxpatterns.com/week-related-calculations/ (look for moving average)

[@seang2012]

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!

[@adityaupadhyay3242]

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?

[@SQLBI]

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/

[@meghamaniar7556]

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?

[@MS-im2zy]

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?

[@nelseify]

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?

[@SQLBI]

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/

[@padmabayyarapu2629]

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

[@devallamahesh989]

How to caluculate previous year rolling averages

[@aarushisinha8814]

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

[@kalinduSekara]

Bravo 👏