Moving Average

[lt79_Pax]

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.

[Anonymous]

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]

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.

[Anonymous · Accepted solution]

Hi @lt79_Pax 

You may refer to the blog from this link: https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

It may help you to have a deeper understanding in filter context and row context.

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]

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.

[harshnathani]

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)

[harshnathani]

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)