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)

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Phil_Seamark you, sir, are a HERO.

 

I was trying to create moving averages using dates, but every time I filtered the date to show only the last 12 months (and I have 5 years of data, so it needs filtering) the measure would crash. Using an index solved the problem, I would never think of this.

 

Thanks for sharing.