DAX for Power BI: 12 Month Rolling Average

[@BIElite]

Make sure to use a negative interval such as -28 when using the DATESINPERIOD function. This ensures that you are averaging the past 28 days and not the future!

[@danieltamasi7517]

Exclamation mark for this comment, it's crucial 👍

[@JidduVillarin]

hahahahaha I was going crazy trying to figure out why it wasn't working for me when I thought about making the value negative. I came back to make a comment about it but I apparently never bothered to check the author's commentary. Kudos on this tip. Highly appreciated.

[@hey_rae]

So the formula in your video should have been -28, not '28'?

[@dachongsh]

Hi, is a great video! However I encountered this problem when follow your formula "A date column containing duplicate dates was specified in the call to function 'DATEINPERIOD'. This is not supported." I am sure my date field cannot be duplicated because is a datetime, can you please advise?

[@lxurettxdenison9862]

I'm having the same problem, has anyone found a solution please?

[@alter8732]

Same issue here.

[@SandeepPawar1]

Thanks. Quick Measures can also be used easily to achieve same result.

[@cognosauthor887]

Dude, keep cranking out these bitesize clips, very useful and informative, saved my life too. Much love and most appreciated.

[@BIElite]

Thanks Cognos! I appreciate the big support

[@cognosauthor887]

@@BIElite Hey man, I need your help, I've been working on this idea of creating either a single cell, single column table based on a selected value from another column, but can't seem to make it work. Summary, what I want to do as an example is capture the selected value of, say for example a date value, then use that value for other calculations, such as getting its previous month etc, then using this in other calculations, any ideas?

[@robertfisch6392]

what about a moving average for the last 3 months on a total quantity. example month-1, month-2, month-3 (100, 300, 200= avg = 200). Not looking for Distinct count. thanks for you help

[@lukica95]

What if we have multiple slicers that could affect our data? Should we use the ALLSELECTED function to include only the filtered items? Thanks in advance! :)

[@BIElite]

Yes, you're correct! Good addition to the logic

[@weipingchong21]

hello, how may we use the allselected function to augment the DAX argument? I've a date slicer and the moving average goes haywire when i changes the period

[@liyaliu7710]

Excellent tutorial and very well explained! Looking forward to see more DAX and Power BI tutorials from you!! :)

[@BIElite]

Thanks Liya! I have several on the channel and a DAX training course coming soon :)

[@jasontan4730]

Please post the URL to download the sample dataset and PowerBI file. Would like to follow the tutorial and practice those formulas. Thanks

[@BIElite]

Hi Jason, I would recommend looking into my training courses offered over on training.bielite.com/. You will gain access to a live SQL Server database to follow along with the lessons.

[@jmwill2005]

The video was a great help. I'm now facing the issue, that I want to display only a portion of the data, e.g. the last 6 months. However the rolling average than also doesn't take into account any more the data from before that time, which makes the values at the very beginning of my time frame not an average of the last 6 months anymore but, just on the piece of data which is still within the range which I display. Basicly it means the closer the data come to the start of the time frame, the more they depend on just individual days. This happens in both scenarios: setting the range for the x-axis or introducing a slicer for the time range to display. Is there are way to decouple the calculation from the displayed range in the visual? I thought about a filter(all) somewhere, but not sure about the logic and syntax to do it.

[@jmwill2005]

Some one asked me if I found a solution. Unfortunately I didn't. But I have to admit, that I'm not using Power BI anymore (at least not currently).

[@Gunterlund583]

how do you handle month that have no sales .. for instance I have sales in jan march and july... what your model is doing is dividing the sum by 3 instead of 12

[@Gunterlund583]

hah never mind figured it out

[@nathanielwang3894]

Is it possible to have a video to calculate stock ratio with rolling 3 month avg sales? Thanks

[@lbhung22]

Is it possible to take this one step further and perform a period over period calculation (% change from one moving average period over prior moving average period)? Any advise would be greatly appreciated!

[@Laxmanmane007]

Thanks for amazing video I have one question suppose I have 4-5 years data and if select January month then how to show 12 months data because year is changing like Jan 2020 then 2019 12 months

[@WadoodAbdul-im6gz]

Thanks so much but I am getting count as 1 and sales as same. Can you help please.

[@hello-u2w]

how would you do this with a year-week number? I dont have a date, but I have a 2020-1, 2020-2 , etc. The values are summed to the week level. thank you!

[@sirlantsalot]

This video was super helpful--I was having trouble using the build in time intelligence / quick measure capabilities to create a rolling average, but this worked superbly! Thank you.

[@BIElite]

Glad to hear it Lance!

[@danbeaston7271]

Great information. However, I am running into an issue with zeros. Instead of calculating as a zero, it will skip the month for a 3 month rolling average. Anyway to include the zeros?

[@Darknesslc3]

= CALCULATE( [sales], DATESINPERIOD( dates[date], MAX( dates[date] ), -3, MONTH) ) / 3 I recommend to have a physical date table with more attributes for better control; also if you want to achieve the same result as what the video shows, i recommend better dax: = AVERAGEX( DATESINPERIOD( dates[date], MAX( dates[date] ), -3, MONTH), [sales] )

[@DanielWeikert]

Is there a good way to do an apples to apples comparison for running totals in certain ranges? e.g. We startet selling in Feb 2017 until jun 2018. Now I like to see the running total in comparison to last year but only feb17 to jun 17 vs. feb18 to jun18. Thanks

[@BIElite]

The SAMEPERIODLASTYEAR function might come in handy here. So if you write your rolling average measure, you can duplicate this measure while adding the SAMEPERIODLASTYEAR to point to the desired date range. Give it a try and let me know if you get it to work!

[@DanielWeikert]

Thanks for your reply. Sameperiodlastyear works. The question is how do I get the start for my latest year for the running totals?

[@BIElite]

If you're using the DATESINPERIOD or DATESBETWEEN functions you could explicitly set the first date of the range to February first of the year you are dealing with. Specifically, you would set it to DATE(year,2,1). To get the year might take some code though. I hope this made sense and gets you on track. If you ever want to discuss more in depth, feel free to send me an email at [e-mail]

[@DanielWeikert]

Thanks for the reply and your offer. Appreciate it!

[@seasundiveseasundive2660]

Would it be better to use a fixed interval as the denominator instead COUNTDISTINCT? For example: -total sales for 1 month (30 calendar days) is USD 10,000 -but those sales happened on 1 day -COUNTDISTINCT would produce 4 week average as US 10,000 -30 calendar days average would be USD 333.33 Please let me know if I've misunderstood the function.

[@giri41]

When I using functions , table and field names don’t appear.only functions. Can any help please???

[@BIElite]

Usually this means you are trying to use functions where they shouldn't be used. Not sure exactly though!

[@alexbehrmann9557]

How would you modify this measure to make a 12 moth rolling average for the day of the week E.g. the average total sales for the past 12 months on Tuesdays (assuming today is Tuesday)

[@BIElite]

I think you could add a clause in the FILTER function using the WEEKDAY function. The default WEEKDAY return number for Tuesday is 3, I believe.

[@damilolaoluwalayomi6201]

Thanks for the tutorial, can I use the DATEINPERIOD function to calculate a 30 day running total instead of the FILTER

[@idakwiatkowska2862]

How can I calculate rolling average for 12 months based on criterium? My data in columns : Name: A, B, C, D Creation day: 02.05.18, 05.03.19... Sales: 1, 0 I have to calculate sales rolling average for 12 months for A and for A+B+C. Any idea? Thanks 😊

[@johnhartley2438]

Thanks for sharing but my rolling average appears as a static line? And I tried the Variables - I've filters applied to my line graph but still a static line.

[@BIElite]

Hey John, this can happen if you are referencing columns from tables that don't have a relationship set up. Hopefully that helps push you in the right direction.

[@martinmejia197]

Thanks! Great Video! It seems that you have your data by Order Sales on daily basis. How would you modify the expression to come up with a 12 Month Rolling Average of the orders value brought into the business on a monthly basis? You would need to aggregate the daily orders into a monthly total and then come up with the rolling average, right? How would you do so/ Thanks!!

[@BIElite]

Hey Martin, to use months instead of days you would just need to change "Day" to "Month" and then put 12 for the interval. Let me know if this works for you!

[@martinmejia197]

Thanks for your response. If you change the intervals to Month, Power BI will provide you the moving average of the INDIVIDUAL Order Values over 12 month periods; it will not give you the 12 Month Moving Average of the TOTAL Order Values PER MONTH (the 12MA of the sum of the individual order values for each month). I cant find how to do that! :(

[@BIElite]

I think I follow you... you're looking to sum all of the orders each month, and then take a 12 month moving average of these summed values? That's actually a really interesting problem so I think I'll make tomorrow's video on this. How does that sound?

[@martinmejia197]

You got it! That is exactly what I need. I am new with Power BI and I have been stuck with this problem for over a week. You video will help me a lot! Thank you so much. I look forward to watching it. I will keep an eye on your channel! Thanks again!

[@djpirra]

Please use the function DIVIDE!

[@BIElite]

You're right. This is imperative although I didn't care as much back when I made this video!

[@Kanfumu]

Hello, i really need help. am viewing data for different departments and i want to see percentages for each department.. but what am seeing is the percent share of all department and not for individuals.. e.g department one has 86% but put all departments together, am getting department as 7.12%

[@catherinevaldez2360]

Hi Parker, i used your formula on my data but it returned blank. What could be wrong? Please help

[@cooky123]

I cannot understand why the moving averages are showing higher values than the previous sales. Are you calculating the average for future periods?

[@BIElite]

Wow, good catch! I didn't realize I put a positive interval. Use a negative interval instead.

[@cooky123]

BI Elite got it! Thanks for your videos!

[@glaucomelibeufilho7151]

Why don´t use dax function Averagex ?

[@BIElite]

That's definitely possible but I wanted to avoid going too deep into iterators. Row context can be a tricky subject to understand

[@hello-u2w]

this didn't work for me. I'm summing a percentage instead of total sales. unfortunately, the average line is the exact same as NSF% I'm trying to get the average of (6 weeks and 13 weeks). Any thoughts on what I should do? thanks

[@TanzdaughteroftheKing]

Man, that's some deep voodoo lol... I love it... wow. thanks man... Imma use this concept to calculate my 6 weeks inventory report for powerbi

[@roopalityagi3958]

regarding datesinperiod function , instead of using lastdate function I want to use user selected value. that is I want rolling year data for last 20 days from the selected date. Can you please help

[@amita5138]

Hello , Hope you're doing good, I regularly go through your solutions, I have a query or I can say I need some help from you. I am currently working on ssas cubes one of my model need a avg of a measure(it written on this fact table) and the avg should consider data for current and last 4 weeks of data. I have a fact table( weekend/PO no id/P id) connected to Time dimensions(date/weekend/month/year).

[@rcb3921]

For a more useful tutorial, please include DAX in the comments.

[@alexengel4163]

Hallo, Great channel!! I get a very low running average when using this formula. what could be wrong?

[@BIElite]

Thanks Alex! So it's just lower than expected? Do you possibly have an extra filter in the visual?

[@alexengel4163]

Hi again,.. In general im having issues work with data on a week level. When i do, the moving average is in days but i need weeks. is there a great way to bring data and calculations to week level? When using a datetable i dont get the right results :( i hope this makes sense?

[@BIElite]

+Alex Engel if I understand your situation correctly, I think you need to create a new calculated column that calculates the first day of the week. For example, if 1/1/2018 is monday, then 1/1/2018 would be 1/1/2018. 1/2/2018 would go to 1/1/2018. The following week, the data would fall back to 1/8/2018. Then maybe try this calculation out. Let me know if that helps any

[@Yogesh.Suryavanshi]

Awesome :) Thanks - could you please guide how to calculate weekly average if data is presented weekly and not daily? - -- tons of thanks 🙏

[@AnatolyVishnevsky]

It was very useful, thanks.

[@BIElite]

Thanks for watching Anatoly

[@gaid81]

Realy thanks

[@andreikilla]

How to do this BY Product? Rolling average for each individual Product?