topic WINDOW function doesn't work with the selection of filters in the report in DAX Commands and Tips

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

IuliaB — Tue, 28 Nov 2023 15:27:12 GMT

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.

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!

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

lbendlin — Fri, 01 Dec 2023 02:23:42 GMT

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-Forum/ba-p/963216
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/1447523

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

pandbh1989 — Mon, 20 May 2024 10:17:30 GMT

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

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

lbendlin — Mon, 20 May 2024 13:03:42 GMT

Seems to work fine for me