Filter causes measure to fail

Question

I am relatively new to PBI and having difficulty debugging this DAX.

SRS Seat Change = 
Var Startdate = SELECTEDVALUE('Combined data'[Source Date])

Var Prevdate =
    CALCULATE(
        MAX( 'Combined data'[Source Date]),
        ALLSELECTED('Combined data'),
        KEEPFILTERS('Combined data'[Source Date] < Startdate)
    )
Var Result =
   CALCULATE(
        SUM(
        'Combined data'[Seats]),
        'Combined data'[Source] = "MNL",
   
        'Combined data'[Source Date] = Prevdate
    )
RETURN

IF(
    result <> 0,
    IFERROR(
        [SRS Seats] - Result,
        blank()
)
)

My matrix table has 2 or more columns which are dates and the above needs to calculate the difference between the columns. if I put a filter on the matrix table the calculation does not work unless I change Prevdate above to a hard coded date so = value("01/01/2024). It does however work fine if I do not filter the Matrix table.

I have tried to keepfliters, allselected but nothing seems to work.

What am I missing? appreciate any help you can offer.

Answer 1

Try like this instead:

SRS Seat Change = 
VAR StartDate = 
    CALCULATE(
        MAX('Combined data'[Source Date]),
        ALL('Combined data')
    )

VAR PrevDate =
    CALCULATE(
        MAX('Combined data'[Source Date]),
        FILTER(
            ALL('Combined data'),
            'Combined data'[Source Date] < StartDate
        )
    )

VAR Result =
    CALCULATE(
        SUM('Combined data'[Seats]),
    'Combined data'[Source] = "MNL",
        FILTER(
            ALL('Combined data'),
            'Combined data'[Source Date] = PrevDate
        )
    )


RETURN

IF(
    result <> 0,
    IFERROR(
        [SRS Seats] - Result,
        blank()
)
)