Remove Filters within Window functions

[pandbh1989]

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 by -1.

In my Visual i want to display latest 7 weeks sales and their moving average.

Any idea how to ignore filters on visual or page while using a window function

---------INPUT DATA---------

Weeks           Sales      Moving Avg (Rolling 3 weeks)

0                    50           60

-1                   60           70

-2                   70           80

-3                   80           90

-4                   90           100

-5                   100         110

-6                   110          120

-7                   120           130

-8                   130           140  

-9                    140          150

------------------------Expected OUTPUT-------------------

Weeks           Sales      Moving Avg (Rolling 3 weeks)

0                    50           60

-1                   60           70

-2                   70           80

-3                   80           90

-4                   90           100

-5                   100         110

-6                   110          120

--------------------OUTPUT Achieved --------------

Weeks           Sales      Moving Avg (Rolling 3 weeks)

0                    50           60

-1                   60           70

-2                   70           80

-3                   80           90

-4                   90           100

-5                   100         110

-6                   110          110

Have a filter on visual to include weeks till -6,  after using window function as below  value for moving average for week -6 is same 110 but ideally we expect 120.

Measure =  AverageX(  Window(-2, REL,0, REL, SUMMARIZE( ALL(DATA), [Weeks]), Order By ([Weeks], ASC) ), SUm(SAles) )

[sanalytics · Accepted solution]

@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