Filter - ALL, ALLSELECTED, ALLEXCEPT

Question

I use the formula below to calculate change from previous week. This works well when no filters are applied (or only applied to the 3 categories I have in the formula - NAME, BRAND and active_status). Is there a way to make this work regardless of what filters are applied? I can inlude every possible filter category in the formula, but it seems like there should be an easier way. I've tried variations of ALL, ALLSELECTED and ALLEXCEPT, but nothing has worked so far.

CHANGE =

VAR lastWeekAmount =

CALCULATE (

[COMPLETE %],

FILTER(ALLEXCEPT('Stop', Core[NAME], Core[BRAND], Core[active_status]), Stop[TOTAL WEEK #]

= MIN (Stop[TOTAL WEEK #])-1))

RETURN

[COMPLETE %] - lastWeekAmount

v-lili6-msft · Accepted Answer

hi&nbsp;Anonymous&nbsp;
You could try this formula
CHANGE =VAR lastWeekAmount =&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [COMPLETE %],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALL ( Stop[TOTAL WEEK #] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Stop[TOTAL WEEK #]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = MIN ( Stop[TOTAL WEEK #] ) - 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; )RETURN&nbsp;&nbsp;&nbsp; [COMPLETE %] - lastWeekAmount
&nbsp;
or
&nbsp;
CHANGE =VAR lastWeekAmount =&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [COMPLETE %],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALLSELECTED ( 'Stop' ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Stop[TOTAL WEEK #]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = MIN ( Stop[TOTAL WEEK #] ) - 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; )RETURN&nbsp;&nbsp;&nbsp; [COMPLETE %] - lastWeekAmount
&nbsp;
If not your case, just share a simple sample pbix file and the expected output in different scenarios.
&nbsp;
Regards,
Lin

Forum Discussion

Filter - ALL, ALLSELECTED, ALLEXCEPT

1 Reply

Related content

Filtering with ALLEXCEPT()

AllSelected inside AllExcept

Filter and Allexcept

Combining AllSELECTED with ALLEXCEPT

ALLSELECTED - ALL - ALLEXCEPT Confusion