Forum Discussion

Jason_Walker's avatar
Jason_Walker
Frequent Visitor
2 years ago
Solved

Trouble Combining Two Filters

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's avatar
    AlexisOlson
    2 years ago

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

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

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