cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

Moving Average

Hi,

 

I've just started using PowerBI and I'm a massive fan of the tool. I've been trying to calculate a simple moving average for Months vs Sessions.

 

I've tried quite a lot of things, googling, etc for a few hours right now and I've read and tried to apply this link (http://community.powerbi.com/t5/Developer/How-to-calculate-Moving-Average-based-on-a-Rolling-10-hour... but everytime I input and edit to my means I receive the errors;

 -Can't resolve a valid name.

-Something like "can't find row being referred to".

 

I've tried creating it as a measure, as a column, decomposing the equation into multiple columns/functions, etc. I am at a bit of a wall and I would greatly apprecaite any help.

 

It's google analytics data so it looks roughly like this:

Month of the YearSessions
1100
2150
3200
4250
5300
6350
7400
8450
9500
10550
11600
12650

I'm quite comfortable transforming and shaping the data in excel, but I would really like to be able to shape my data in powerbi as it seems I've just misunderstood how to apply a dax expression as opposed to do all my data transformation in excel and then simply importing.

 

Any help would be much appreciated.

27 REPLIES 27

In my application this approach collected an extra month's worth of data. The corrected forumula which worked for me is below. I tested this with manual calculations in Excel.

 

[Trailing 12 Month Average ] = DIVIDE(
CALCULATE(
SUM(Avg_sales[Sales_Count]),
DATESBETWEEN(
Avg_sales[List_Date],
FIRSTDATE(DATEADD(Avg_sales[List_Date],-11,MONTH)),
LASTDATE('Avg_sales'[List_Date])
)
),12)

Did you find the solution? Facing the same problem

Hi, how can this be calculated if there are duplicate dates?

I have a DateTime column which has values for different timestamps on the same date. All the values are to considered while calculating the Average.

Hello, @v-sihou-msft 

 

I exactly followed yoru approach but my moving averages are the same as the nomal averages for each month. It looks like other people are having the same issues. Any insights?

 

Thanks!

I have the same question. How can you remove the initial values, as they're not truly averages.

Anonymous
Not applicable

Hi @v-sihou-msft

 

I'm a new user as well and have hit a snag while calculating moving average for weeks. Firstly, It's a great formula that you've put up for calculating the moving average of -3 months!

 

But, I'm trying to do the same thing with weeks. I have my raw data in daily entries. I'm trying to get the average per week and then take the moving average among weeks. My moving average interval would be -2 and +2 weeks. How do I do this? (as the datesinperiod function doesn't have the option to mention intervals in weeks!). 

 

Thanks in advance and much appreciated!

 

Regards,

sshroffm

Hi all,

 

Very useful. However I'm still getting incorrect results in my model. For convenience I have renamed my data in accordance with the exmple in this thread.

 

I have multiple values per each date so my rolling average is too low, I need to calculated it based on the total for each date not based on the each value within the date. 

 

Any suggestions

 

scrrenshot of my model 

 

many thanks

For anyone having issues where the MA is the same as the regular averages, check what date column you have in the "datesinperiod" formula. Make sure the date column you reference here is exactly the date column you have pulled into your visual/Table. Common error here is to reference a diffferent datecolumn.

Maybe this will help.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors