Trouble Combining Two Filters

[Jason_Walker]

I have a table with sales obligations over the current fiscal year (starting in October) that I want to compare to a couple other tables with prior fiscal year data. I finally got the fiscal months to show cumulative results AND to be in chronological order using the measure below:

Cumulative Obs =

VAR CurrentMonth = MAX('Fiscal Month'[Fiscal Month])

RETURN

CALCULATE(

SUM('FY24 Execution'[Oblig.]),

FILTER(

ALL('Fiscal Month'),

'Fiscal Month'[Fiscal Month] <= CurrentMonth

)

)

Now I would like to hide/delete any future fiscal month data for the current fiscal year in the visual so the data looks cleaner. Currently, the visual looks like this:

I created a custom column [DatesWithObligations] in Power Query to check whether the [Posting Date] from my table is <= the current date.

= Table.AddColumn(#"Renamed Columns2", "DatesWithObligations", each [Posting date] <= DateTime.Date(DateTime.LocalNow()))

Lastly, I tried to add a filter to the measure from above that would remove any future fiscal months using [DatesWithObligations].

Cumulative Obs =

VAR CurrentMonth = MAX('Fiscal Month'[Fiscal Month])

RETURN

CALCULATE(

SUM('FY24 Execution'[Oblig.]),

FILTER('FY24 Execution','FY24 Execution''[DatesWithObligations] = "TRUE"),

FILTER(

ALL('Fiscal Month'),

'Fiscal Month'[Fiscal Month] <= CurrentMonth)

)

The measure above gives the same results with future fiscal months still showing as a straight line. I'm sure I have the syntax of the measure messed up but I can't figure out how to both aggregate obligations by fiscal month and remove future fiscal month obligations.

Any ideas?

[AlexisOlson · Accepted solution]

Maybe you can check to see if any dates in the current month have obligations and condition the result on that. Something like this:

Cumulative Obs =
VAR CurrentMonth = MAX ( 'Fiscal Month'[Fiscal Month] )
VAR IsValidMonth = MAX ( 'FY24 Execution'[DatesWithObligations] )
RETURN
    IF (
        IsValidMonth,
        CALCULATE (
            SUM ( 'FY24 Execution'[Oblig.] ),
            FILTER (
                ALL ( 'Fiscal Month' ),
                'Fiscal Month'[Fiscal Month] <= CurrentMonth
            )
        )
    )

(This implicitly returns blank if IsValidMonth is false.)

[Jason_Walker]

Ya da man! Thanks, that worked perfect.