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

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.