cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ElliotP Member
Member

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

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Moving Average

@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
23 REPLIES 23
Super User
Super User

Re: Moving Average

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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
sdjensen Senior Member
Senior Member

Re: Moving Average

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
ElliotP Member
Member

Re: Moving Average

Hey,

 

I really appreciate the responeses especially so quickly.

 

I'm not looking to do anything with Pivottable tbh, I might be missing something; but I was hoping for some direction in terms of DAX transforming data within PowerBI.

 

The links are in part useful in that they allow me understand a little bit and different approach to filtering the data.

 

I grabbed dates from analytics, set them as dates, but so I can try the datesbetween approach, but I'm still not sure and I'm unable to get past the error values.

 

I'm going to spend some time tonight reading upon on dax and creating formulas. Any help would be greatly appreciated!

Moderator v-sihou-msft
Moderator

Re: Moving Average

@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
ElliotP Member
Member

Re: Moving Average

Thank you so much for the responses.

 

@v-sihou-msft Thank you so much.

 

@sdjensen I took your advice and looked into creating its own date table, extremely useful!

 

I used this code to create a 10 day Simple Moving average:

Moving_Average_3_Months = 
CALCULATE (
    AVERAGEX ( 'All Web Site Data', 'All Web Site Data'[Sessions] ),
    DATESINPERIOD (
        'Table123'[DateKey],
        LASTDATE ( 'Table123'[DateKey] ),
        -10,
        DAY
    )
)

Link: https://gyazo.com/bb02f6541c6f0e99c4477f6b4ead42cd

 

The drill down feature doesn't seem to want to work for me. Should I simple create my own hierarchy for this?

arunksri Visitor
Visitor

Re: Moving Average - Please Help!

Hi,

 

I am trying to calculate moving 3 month average and came across this post. I followed the steps exactly as mentioned in the post but no luck so far. My formula looks like this...

 

MA_3M = CALCULATE(AVERAGEX('Session', 'Session'[Sessions]), DATESINPERIOD('Session'[FullDate], LASTDATE('Session'[FullDate]),-3,MONTH))

 

While seeing the results, the Sessions and the Moving average show the same numbers....what am I missing???

 

Pleae help!

Super User
Super User

Re: Moving Average - Please Help!

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



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

Re: Moving Average

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

Highlighted
Super User
Super User

Re: Moving Average

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