locked
Moving average DAX RRS feed

  • Question

  • Here is what my data looks like:

    date | sales-amount

    date1 | 100

    date2 | 50

    date3 | 25 

    date4 | 25 

    date5 | 50

    How can I get to following output using DAX?

    date | sales-amount | moving-average-3-days

    date1 | 100 | -

    date2 | 50 | -

    date3 | 25  | -

    date4 | 25  | 58.33

    date5 | 50 | 33.3

    Thanks.

    Wednesday, June 5, 2013 9:34 PM

Answers

  • You use the formulas for measure in pivot table instead of column measure.

    I make sample to share on skydrive.

    https://skydrive.live.com/#!/edit.aspx?cid=94B99C319E63236A&resid=94B99C319E63236A!110&app=Excel

    Regards

    • Edited by CDzung Thursday, June 6, 2013 2:22 PM
    • Marked as answer by powerpivotq Thursday, June 6, 2013 3:52 PM
    Thursday, June 6, 2013 2:16 PM

All replies

  • Hi Powerpivotq,

    If you use date as date format instead of text format. You can use below measure:

    =IF(COUNTROWS(
            DATESINPERIOD(Table1[Date],
                LASTDATE(Table1[Date]),-3,day 
            )
         )<3,
         BLANK(),
         AVERAGEX(
                SUMMARIZE(
                         DATESINPERIOD(Table1[Date],
                                        LASTDATE(Table1[Date]),-3,day
                         ),
                         Table1[Date],
                         "SumSA",
                         CALCULATE(
                                     SUM(Table1[Sales-amount])
                                     
                        )
                ),
               [SumSA]
         )
     )
    Regards,

    
    
    
    Thursday, June 6, 2013 6:48 AM
  • Thanks for the reply. I tried that but it did not work, can you help again:

    Thursday, June 6, 2013 1:40 PM
  • You use the formulas for measure in pivot table instead of column measure.

    I make sample to share on skydrive.

    https://skydrive.live.com/#!/edit.aspx?cid=94B99C319E63236A&resid=94B99C319E63236A!110&app=Excel

    Regards

    • Edited by CDzung Thursday, June 6, 2013 2:22 PM
    • Marked as answer by powerpivotq Thursday, June 6, 2013 3:52 PM
    Thursday, June 6, 2013 2:16 PM
  • Perfect it works!!!!!! Thanks you....
    Thursday, June 6, 2013 3:52 PM