Forum Discussion

lt79_Pax's avatar
lt79_Pax
Helper II
6 years ago
Solved

Moving Average


Dear Power BI Representative,
I have a question regarding moving averages.  How does the period to use in a moving average work when it filters for the last x amount of days that have a defined month day and year?  How is it moving? I am reviewing the following function within the "Definitive Guide to Dax" book that defines a moving average over the last 30 days.  If the last 30 days = 12/1/2011 through 12/31/2011,  how is it moving when all of the dates listed are in December 2011?  Are filter contexts making that interval relative to the model and if so how?  I do not understand.  Please advise.
AvgXSales30 =
VAR LastVisibleDate = MAX ('Date'[Date] )
VAR NumberofDays = 30
VAR PeriodtoUse =   
    FILTER (
            ALL ( 'Date' ),
            AND (
                'Date'[Date] > LastVisibleDate - NumberofDays,
                'Date'[Date] <= LastVisibleDate
            )
        )
VAR Result =
    CALCULATE (
        AVERAGEX ( 'Date', [Sales Amount] ),
        PeriodtoUse
    )
RETURN
    Result
 
Thank you.

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi lt79_Pax ,

     

    Article on Moving Averages

    https://tradebrains.in/moving-averages-indicator/

     

    Now, a simple moving average (SMA) is a calculation that considers the average or arithmetic mean of a given set of prices over a specific time period. 

     

    Suppose the 30 Day Moving Average for 31st Dec , 2011 is "X" which takes the price from 12/1/2011 to 12/31/2011.

     

    Now the next day i.e 1st of january 2012, the 30 day moving average will be proce from 12/2/2011 to 1/1/2012.

     

    and this is what the formula is doing - >   'Date'[Date] > LastVisibleDate - NumberofDays && 'Date'[Date] <= LastVisibleDate

     

     

    Hope i was able to explain.

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

     

     

     

  • lt79_Pax's avatar
    lt79_Pax
    Helper II
    6 years ago

    Hi Harsh,
    Thank you for responding.  Let me see if I understand this correctly. Therefore when the variable of 
    VAR LastVisibleDate = MAX ('Date'[Date] ) is placed in the measure under the variable
    VAR PeriodtoUse =
    FILTER (
    ALL ( 'Date' ),
    AND (
    'Date'[Date] > LastVisibleDate - NumberofDays,
    'Date'[Date] <= LastVisibleDate
    ) 
    this maximum is a scalar value. When it is used in the averagex expression this expression then makes it possible to show a moving period of time based on what is being displayed in the graph across a larger range of time as the averagex is computed it iterates on the sales amount relative to the filter contexts of the model. The filter just acts as a means of obtain a set range of time i.e. 30 days.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi lt79_Pax ,

       

      So Last Visible Date is the Date of the current Row.

       

       

      So for each row you are calculating average of 30 days backwards from that date. 

       

      Date'[Date] > LastVisibleDate - NumberofDays,   
      'Date'[Date] <= LastVisibleDate

       

      Regards,
      Harsh Nathani
      Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi lt79_Pax 

    I build a Sales table to have a test.

    Firstly I test your Formula.

    But the last value in result is incorrect, the right answer should be 4740/31 = 152.90:

    You can try my measure.

    Measure = 
AVERAGEX (FILTER(ALL( 'Date'),'Date'[Date]<=MAX('Date'[Date])), [ Sales Amount])

    Result:

    Max('Date'[Date])  will return to the Date value in this row. For example, the date value in this row is 2011/12/29, so 

    FILTER(ALL( 'Date'),'Date'[Date]<=MAX('Date'[Date])) will return to a table the date <= 2011/12/29, and then caluclate the average.

    For more info to the Function used in dax: Averagex Max Filter

    You can download the pbix file from this link: Moving Average

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

    • lt79_Pax's avatar
      lt79_Pax
      Helper II
      6 years ago

      Hi Rico,

      Thank you for resonding.  This is interesting since the initial expressions I provided came directly from 'The Definitive Guide to DAX' book.  I was actually more interested in the back end process of how the expressions were allowing the average to move and how and where the contexts, filter or row, were taking place in these processes.

       

      Thank you for the update.