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 DAX inserts all the missing days and calculations get messed up.

Here is an example from Excel. 

Can you please help me replicate this result in DAX?

Thank you!

 

Date Sales 3PeriodMovingAverage

06/21/1816 
06/24/189 
06/26/1810                                          11.67
07/01/189                                             9.33
07/02/187                                             8.67
07/05/1813                                             9.67
07/08/1817                                          12.33
07/12/1811                                          13.67
07/13/189                                          12.33
07/18/1811                                          10.33
07/20/1813                                          11.00
07/21/1812                                          12.00
07/26/188                                          11.00
07/29/1818                                          12.67
08/02/1819                                          15.00
08/03/1815                                          17.33
08/04/1814                                          16.00
08/08/1817                                          15.33
08/10/1811                                          14.00
08/15/1819                                          15.67
08/19/1817                                          15.67
08/21/1814                                          16.67
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)

7 Replies

  • 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
      8 years ago

      Phil, thank you so much!

    • 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

       

    • 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.