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
IuliaB
New Member

WINDOW function doesn't work with the selection of filters in the report

Hello,

 

I have a report where I need to calculate and display a moving average.

My table looks like the one below. It includes issues with their changes in status and the time each issue spent in each status.

 

IuliaB_0-1701184117797.png

 

For each issue I need to calculate an average of a window of 5 issues (2 issues before, the issue, 2 issues after ordered by Update DateTime) .

I used the following formula:

MA = CALCULATE

           ( [Average TimeDays],

              WINDOW(-2,REL,2,REL,

                               ALLSELECTED(FactIssueStatus),

                                ORDERBY(FactIssueStatus[Update DateTime], ASC),

                                PARTITIONBY(FactIssueStatus[StatusName])

                              )

            )

 

The formula seems to be working when  is applied on the entire table, however when we are using the slicers in the report, it doesn't work anymore. 

I assumed it's because of the ALLSELECTED() used in the formula. I treid to replace it with a CALCULATETABLE(FactIssueStatus, FactIssue[StatusName]=[Selected Status]), where [Selected Status]=SELECTEDVALUE(FactIssue[StatusName]) with no success.

 

Is it possible to adjust the formula so that it will work also when using filters in the report?

 

Thank you!

3 REPLIES 3
pandbh1989
New Member

Hi,

I have a similar issue/requirement.  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) )

 

 

 

 

 

Seems to work fine for me

 

lbendlin_0-1716210213723.png

 

lbendlin
Super User
Super User

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

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.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.