Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

1 ACCEPTED SOLUTION

@ElliotP

 

Firstly you need a column of date with full date format. Then you can use calculated measure to get the expected result. Please refer to following steps.

 

  1. Create a calculated column for the date.
    FullDate =
    DATE ( 2016, 'Session'[Month of the Year], 1 )
    
  2. Create a measure for 3 months moving average. You can change the number of months if you want.
    Moving_Average_3_Months = 
    CALCULATE (
        AVERAGEX ( 'Session', 'Session'[Sessions] ),
        DATESINPERIOD (
            'Session'[FullDate],
            LASTDATE ( 'Session'[FullDate] ),
            -3,
            MONTH
        )
    )
    
  3. Drag the Line Chart into your canvas as below.
    125.jpg

View solution in original post

30 REPLIES 30

Are you using a calendar table?  http://exceleratorbi.com.au/power-pivot-calendar-tables/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

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?

sdjensen
Solution Sage
Solution Sage

Hi,

 

I would recommend to read this article.

 

Basically you need to create a period table containing all the dates in your data set and then connect your table with your values to this period table. The article talk about marking your table as a period table - this is not needed in Power BI it's only needed in Power Pivot for Excel. There is many awesome articles about how you can create a period table in Power BI - I like this one.

 

If you only have a month value in your values table then you need to convert this into a date to be able to create the relationship from this table to your period table - just pick the first day of the month.

/sdjensen

There are a lot of foundational things you should do. 

 

1. Set up a calendar table. Read my article about that here http://exceleratorbi.com.au/power-pivot-calendar-tables/

2. Join the data table to the calendar table. Read about data shape here http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

 

A moving average would then normally use a measure (not calc column) something like this

 

Total Sales rolling quarter :=
CALCULATE (
Sum(data[qty]),
FILTER (
ALL ( Calendar ),
Calendar[MonthID] >= MAX (calendar[MonthID]) -2
&& Calendar[MonthID] <= MAX ( Calendar[MonthID] )
)
)

 

There is a lot to learn if you want to understand it 🙂

 

btw, your data looks like a running total, not a monthly qty, so you may need something different. 



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

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

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.

Dear Matt, i though that since time intelligence functions implemented in DAX calendar tables are not necessary anymore

I'm not sure what you mean. You definitely need a calendars table if you want to use the inbuilt time intelligence functions. 



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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.