Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
pandbh1989
New Member

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 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) )

 

 

1 ACCEPTED SOLUTION
sanalytics
Super User
Super User

@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

 

sanalytics_0-1716218804869.png

 Hope it will help you.

 

Regards

sanalytics

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

View solution in original post

1 REPLY 1
sanalytics
Super User
Super User

@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

 

sanalytics_0-1716218804869.png

 Hope it will help you.

 

Regards

sanalytics

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.