Forum Discussion

lt79_Pax's avatar
lt79_Pax
Icon for Helper II rankHelper 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 mo...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
lt79_Pax
Icon for Helper II rankHelper 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)