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
BEchagueSNTX
Frequent Visitor

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

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.

 

 

CALCULATE(
    SUM('FORECASTxSALES'[SALES]),
    FILTER(
        ALL('CALENDAR'),
        ( 
            ('CALENDAR'[FiscalYearOrder] = MAX('CALENDAR'[FiscalYearOrder])) &&
            ( VALUE('CALENDAR'[FiscalMonthOrder]) <= MAX('CALENDAR'[FiscalMonthOrder]))
        )
    )
)

 

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.

 

1 REPLY 1
Wilson_
Super User
Super User

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

 

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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.