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.