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

      • MattAllington's avatar
        MattAllington
        Community Champion
        10 years ago

        The 2 concepts "you must have a calendar table to use inbuilt time intelligence" and "you don't have a mark as date table feature in Power BI" are completely independent. You do need a calendar table if you want to use inbuilt time intelligence. The reference at SQLBI is related to the use of surrogate keys between a date table and a data table. In Excel you can use surrogate keys and use the "mark as date table feature". In Power BI you must use a date column.