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.
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
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.
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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!

  • hourir2's avatar
    hourir2
    Advocate I
    5 years ago

    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.