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

Dear @MattAllington I'm sorry to disapoint You, but You don't need it anymore:

"When you create a model in Power Pivot or Analysis Services Tabular, you can apply the setting “Mark as Date Table” choosing a column of Date data type as the date in the table."

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

 

I'll be grateful if You'll shortly give a prove that i'm not right.

Sincerely Yours, Max

The 2 concepts "you must have a calendar table to use inbuilt time intelligence" and "you don't have a mark as date table feature in Power BI" are completely independent. You do need a calendar table if you want to use inbuilt time intelligence. The reference at SQLBI is related to the use of surrogate keys between a date table and a data table. In Excel you can use surrogate keys and use the "mark as date table feature". In Power BI you must use a date column. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Is it possible to remove the first two months? @v-sihou-msft 1/1/2016 and 2/1/2016 average 100 and 125 are not the numbers I want to include in my report.

Anonymous
Not applicable

Trailing_Average_3_Months = CALCULATE(AVERAGEX('Active List Table', 'Active List Table'[Sales_Pric]),DATESINPERIOD('Active List Table'[List_Date],LASTDATE('Active List Table'[List_Date]), -3, MONTH))

 

In my case this is not work. 

Hi @arunksri, @Anonymous,

 

I use a similar formula and am not able to get the moving average.

Were you guys able to figure out what the problem was?

Maybe I am having the same problem as you guys.

 

Thanks,
Preetish

Anonymous
Not applicable

via below logic, you can find the Trailing average of the 12 months. 

 

 

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

 

 

Thank you @Anonymous . I just got it working with a similar DAX expression.  

I have the same problem:

Table with one column as Survey with values 0, 1, -1

Another column as Date, in date format "m/d/Y".

 

The 3 month moving average is the same as the average. I've tried multiple things but still stumped. Woman Sad help?

Hello,

 

I followed the steps above to create the moving average however I get an error stating that there are duplicates in my DATESINPERIOD column (this is because there are and there are supposed to be...). I was hoping it would add all the values for one day together and then average these - do you have any suggestions?

 

Thanks 🙂

 

Max

 

 

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)

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