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.
ElliotP's avatar
ElliotP
Post Prodigy
10 years ago

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's avatar
    arunksri
    Regular Visitor
    10 years ago

    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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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. :womansad: help?

    • Preetish_1's avatar
      Preetish_1
      Helper I
      9 years ago

      Hi arunksri, Anonymous,

       

      I use a similar formula and am not able to get the moving average.

      Were you guys able to figure out what the problem was?

      Maybe I am having the same problem as you guys.

       

      Thanks,
      Preetish

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        via below logic, you can find the Trailing average of the 12 months. 

         

         

        [Trailing 12 Month Average ] = DIVIDE(
        CALCULATE(
        SUM(Avg_sales[Sales_Count]),
        DATESBETWEEN(
        Avg_sales[List_Date],
        FIRSTDATE(DATEADD(Avg_sales[List_Date],-12,MONTH)),
        LASTDATE('Avg_sales'[List_Date])
        )
        ),12)

         

         

    • Max01's avatar
      Max01
      Helper I
      8 years ago

      Hello,

       

      I followed the steps above to create the moving average however I get an error stating that there are duplicates in my DATESINPERIOD column (this is because there are and there are supposed to be...). I was hoping it would add all the values for one day together and then average these - do you have any suggestions?

       

      Thanks :)

       

      Max