How do I use a filtered table to filter it's not filtered self in a FILTER() function

BEchagueSNTX — Fri, 31 Mar 2023 15:19:33 GMT

This measure calculates the actual fiscal year sales up to the current month.
As you can see 'CALENDAR'[FiscalYearOrder] is the field that contains the fiscal year, so I make sure that it is equal to the MAX fiscal year, wich will be the current year.
Then I make sure that 'CALENDAR'[FiscalMonthOrder] wich is the fiscal month in order from 1 to 12, is less or equal to the actual month's fiscal month order.

CALCULATE( SUM('FORECASTxSALES'[SALES]), FILTER( ALL('CALENDAR'), ( ('CALENDAR'[FiscalYearOrder] = MAX('CALENDAR'[FiscalYearOrder])) && ( VALUE('CALENDAR'[FiscalMonthOrder]) <= (IF(VALUE(MONTH(TODAY()))>=9,VALUE(MONTH(TODAY()))-8,VALUE(MONTH(TODAY()))+4))) ) ) )

I want to modify this measure so that it takes the maximun FiscalMonthOrder of the filtered CALENDAR table instead the actual month's fiscal month order. The problem is that in this measure I have already used ALL() to remove the filters of the CALENDAR table.

The previous example would return the maximun FiscalMonthOrder of the calendar table, but if the user has filtered the FiscalMonthOrder it wouldn't impact the masure, and I want it to impact.
I want the measure to return the actual fiscal year sales up to the maximun month selected by the user.

Re: How do I use a filtered table to filter it's not filtered self in a FILTER() function

Wilson_ — Sun, 02 Apr 2023 20:54:37 GMT

Hello!

Assuming FiscalMonthOrder is the field the user is selecting in a slicer, I've modified your measure to the below. I created a variable to hold the selected month if the report viewer has picked one and the maximum FiscalMonthOrder (like you originally had) if the report viewer has not.

Please let me know if this works for you or if you were looking for something different. 😄

VAR SelectedMonth = IF ( HASONEVALUE ( 'CALENDAR'[FiscalMonthOrder] ), SELECTEDVALUE ( 'CALENDAR'[FiscalMonthOrder] ), MAX ( 'CALENDAR'[FiscalMonthOrder] ) ) RETURN CALCULATE ( SUM ( 'FORECASTxSALES'[SALES] ), FILTER ( ALL ( 'CALENDAR' ), ( ( 'CALENDAR'[FiscalYearOrder] = MAX ( 'CALENDAR'[FiscalYearOrder] ) ) && ( VALUE ( 'CALENDAR'[FiscalMonthOrder] ) <= SelectedMonth ) ) ) )

topic How do I use a filtered table to filter it's not filtered self in a FILTER() function in DAX Commands and Tips

How do I use a filtered table to filter it's not filtered self in a FILTER() function

Re: How do I use a filtered table to filter it's not filtered self in a FILTER() function