Forum Discussion

ElliotP's avatar
ElliotP
Post Prodigy
10 years ago
Solved

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...
General Comment
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    10 years ago

    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.
MattAllington's avatar
MattAllington
Community Champion
10 years ago

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. 

AlexH's avatar
AlexH
Regular Visitor
6 years ago

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