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

Hey,

 

I really appreciate the responeses especially so quickly.

 

I'm not looking to do anything with Pivottable tbh, I might be missing something; but I was hoping for some direction in terms of DAX transforming data within PowerBI.

 

The links are in part useful in that they allow me understand a little bit and different approach to filtering the data.

 

I grabbed dates from analytics, set them as dates, but so I can try the datesbetween approach, but I'm still not sure and I'm unable to get past the error values.

 

I'm going to spend some time tonight reading upon on dax and creating formulas. Any help would be greatly appreciated!

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!

  • zixindh's avatar
    zixindh
    Regular Visitor
    10 years ago

    Is it possible to remove the first two months? v-sihou-msft 1/1/2016 and 2/1/2016 average 100 and 125 are not the numbers I want to include in my report.

    • keithemill's avatar
      keithemill
      New Member
      7 years ago

      I have the same question. How can you remove the initial values, as they're not truly averages.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi did someone ever replied with a solution for this? I'm also interested in showing just the part with the correct values and not the rump to it

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Trailing_Average_3_Months = CALCULATE(AVERAGEX('Active List Table', 'Active List Table'[Sales_Pric]),DATESINPERIOD('Active List Table'[List_Date],LASTDATE('Active List Table'[List_Date]), -3, MONTH))

     

    In my case this is not work. 

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

  • 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

  • richay's avatar
    richay
    New Member
    5 years ago

    Champion, this helped heaps 😄