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.
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!

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)

     

     

    • salberts's avatar
      salberts
      New Member
      8 years ago

      In my application this approach collected an extra month's worth of data. The corrected forumula which worked for me is below. I tested this with manual calculations in Excel.

       

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

    • Preetish_1's avatar
      Preetish_1
      Helper I
      9 years ago

      Thank you Anonymous . I just got it working with a similar DAX expression.  

    • salberts's avatar
      salberts
      New Member
      8 years ago

      In my application this approach collected an extra month's worth of data. The corrected forumula which worked for me is below. I tested this with manual calculations in Excel.

       

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