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.
MattAllington's avatar
MattAllington
Community Champion
10 years ago

There are a lot of foundational things you should do. 

 

1. Set up a calendar table. Read my article about that here http://exceleratorbi.com.au/power-pivot-calendar-tables/

2. Join the data table to the calendar table. Read about data shape here http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

 

A moving average would then normally use a measure (not calc column) something like this

 

Total Sales rolling quarter :=
CALCULATE (
Sum(data[qty]),
FILTER (
ALL ( Calendar ),
Calendar[MonthID] >= MAX (calendar[MonthID]) -2
&& Calendar[MonthID] <= MAX ( Calendar[MonthID] )
)
)

 

There is a lot to learn if you want to understand it :-)

 

btw, your data looks like a running total, not a monthly qty, so you may need something different. 

  • LsasS's avatar
    LsasS
    Frequent Visitor
    10 years ago

    Dear Matt, i though that since time intelligence functions implemented in DAX calendar tables are not necessary anymore

    • MattAllington's avatar
      MattAllington
      Community Champion
      10 years ago

      I'm not sure what you mean. You definitely need a calendars table if you want to use the inbuilt time intelligence functions. 

      • LsasS's avatar
        LsasS
        Frequent Visitor
        10 years ago

        Dear MattAllington I'm sorry to disapoint You, but You don't need it anymore:

        "When you create a model in Power Pivot or Analysis Services Tabular, you can apply the setting “Mark as Date Table” choosing a column of Date data type as the date in the table."

        https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

         

        I'll be grateful if You'll shortly give a prove that i'm not right.

        Sincerely Yours, Max

  • laltripti22's avatar
    laltripti22
    Frequent Visitor
    8 years ago

    Hi, how can this be calculated if there are duplicate dates?

    I have a DateTime column which has values for different timestamps on the same date. All the values are to considered while calculating the Average.

  • AlexH's avatar
    AlexH
    Regular Visitor
    6 years ago

    Hi all,

     

    Very useful. However I'm still getting incorrect results in my model. For convenience I have renamed my data in accordance with the exmple in this thread.

     

    I have multiple values per each date so my rolling average is too low, I need to calculated it based on the total for each date not based on the each value within the date. 

     

    Any suggestions

     

    scrrenshot of my model 

     

    many thanks