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
6 years ago

Hi v-sihou-msft

 

I'm a new user as well and have hit a snag while calculating moving average for weeks. Firstly, It's a great formula that you've put up for calculating the moving average of -3 months!

 

But, I'm trying to do the same thing with weeks. I have my raw data in daily entries. I'm trying to get the average per week and then take the moving average among weeks. My moving average interval would be -2 and +2 weeks. How do I do this? (as the datesinperiod function doesn't have the option to mention intervals in weeks!). 

 

Thanks in advance and much appreciated!

 

Regards,

sshroffm