Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Moving Average without dates

Hi,   I need to calculate simple moving average for certain dates that do not come into succession, just an average of previous 3 figures, then for next figures, etc.. With date formatted column DA...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    HI Anonymous

     

    One approach is to add an Index column and then use that to generate your "last three items" average.  I have attached a PBIX file (you can hide the Index col if you like).

     

    The index calculated column can be added like this

     

    Index = RANKX('Table1','Table1'[Date],,ASC)

    With the moving average column using it in this way

     

    Moving Average = 
VAR MyIndex =  Table1[Index]
VAR myResult = 
    AVERAGEX(
        FILTER(
            'Table1',
            'Table1'[Index] > MyIndex-3  && 
            'Table1'[Index] <= MyIndex
           ),'Table1'[Value]
           )
RETURN FIXED(myResult,2)

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

HI Anonymous

 

One approach is to add an Index column and then use that to generate your "last three items" average.  I have attached a PBIX file (you can hide the Index col if you like).

 

The index calculated column can be added like this

 

Index = RANKX('Table1','Table1'[Date],,ASC)

With the moving average column using it in this way

 

Moving Average = 
VAR MyIndex =  Table1[Index]
VAR myResult = 
    AVERAGEX(
        FILTER(
            'Table1',
            'Table1'[Index] > MyIndex-3  && 
            'Table1'[Index] <= MyIndex
           ),'Table1'[Value]
           )
RETURN FIXED(myResult,2)

mkr's avatar
mkr
Frequent Visitor
6 years ago

Hi!

 

Very useful post but I have another question. How can I calculate moving average without creating calculated columns and using only measures? I'm connected to SQL analysis server and I cannot create calculated columns. 

 

I'd like to calculate average duration of the last 3 Name_IDs.  My goal is to plot Name_IDs on x axis and Moving average as value. 

Input:

 

 My current attempt:

 

Moving average = 

VAR index = RANKX (
    ALLSELECTED ( NAME ),
    CALCULATE(MAX(Table1[EndDate])), 
    ,
    ASC
)

Return
    AVERAGEX(
        FILTER(
            Table1,
            indexName >index-3  && -- indexName is another measure that is equal to VAR index
            indexName <= index

           ),Table1[Duration]
           )

 

 Thank you!