Forum Discussion

pandbh1989's avatar
pandbh1989
New Member
2 years ago
Solved

Remove Filters within Window functions

Hi,   I am calculating moving average of 3 weeks on a YTD weekly sales data.  Sample Example -  Assuming hypothetical data below arranged by weeks from latest weeks as 0 to prior weeks decremented ...
DAX Window Function
Exclude Filters on visuals
  • sanalytics's avatar
    sanalytics
    2 years ago

    pandbh1989 

    Averagex is an iterator. It triggers row context. We need to do the context transition. So, only SUm(SAles) should not work properly. Wrap this thing with CALCULATE or create a separate measure for Sales.
    Based on your scenario, you need to modify the window settings as well.

    Try the below measure.

    Moving Aveage = 
  AVERAGEX(  
    WINDOW(
    0, REL,
    2, REL,
     SUMMARIZE( ALL('Table'), [Weeks]), 
     ORDERBY([Weeks], ASC) ), [Total Sales]
  )

    Check out the below screenshot if it works

     

     Hope it will help you.

     

    Regards

    sanalytics

    If it is your solution then please like and accept it as solution

sanalytics's avatar
sanalytics
Super User
2 years ago

pandbh1989 

Averagex is an iterator. It triggers row context. We need to do the context transition. So, only SUm(SAles) should not work properly. Wrap this thing with CALCULATE or create a separate measure for Sales.
Based on your scenario, you need to modify the window settings as well.

Try the below measure.

Moving Aveage = 
  AVERAGEX(  
    WINDOW(
    0, REL,
    2, REL,
     SUMMARIZE( ALL('Table'), [Weeks]), 
     ORDERBY([Weeks], ASC) ), [Total Sales]
  )

Check out the below screenshot if it works

 

 Hope it will help you.

 

Regards

sanalytics

If it is your solution then please like and accept it as solution