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

 

 

Hello Phil_Seamark once again :)

 

I am still stuck with the task. I need a average for prices that are collected on a weekly basis, but often the days are omitted, for that I have created an index that reset after change. It has 4 levels - Region, Municipality, Product Category, Product Name - after we move to next municipality with different category and product, index is reset.

When I use your formula on this file, I get huge numbers, I think it is summing everything beneath without taking into consideration index.

Thanks in advance!

 

Best,

Levan