Using ALLEXCEPT to get value outside of current filters/context

[dancarr22]

Hello,

I am encountering difficulties using ALLEXCEPT.   Have also tried ALL and ALLSELECTED but none seem to work.

We have the following dataset:

Security	Date	Amount
XYZ	12/31/2020	468
XYZ	3/31/2021	657
XYZ	6/30/2021	389

The date column of this field joins to the date field in our date table - not sure if that impacts this.

What we need to do is get the MAX(Date) which is LESS THAN the given filters/context dates.

So, if filtered (and context) date range is 1/1/2021-3/31/2022 - we need to return date 12/31/2020 - which is 'outside' of the filtered date dataset.  So, need to remove any filters related to that date column.  
Currently have this:

StartingValueDate = VAR startDate = MIN(SecurityAmounts[Date])

RETURN CALCULATE(MAX(SecurityAmounts[Date]),   ALLSELECTED(SecurityAmounts[Date]), SecurityAmounts[Date] < startDate)

ALLSELECTED comes the closest but doesn't work when multiple securities are selected.  Basically, just need to remove the filter on the Date field for this measure.  Tried REMOVEFILTERS too but that did not work.  Appreciate any assistance which can be provided.

Thanks,

Dan

[CNENFRNL]

Simple enough

[FreemanZ · Accepted solution]

A measure with the code below shall work:

StartingValueDate =

VAR MinDate = MIN (SecurityAmounts[Date])

RETURN

    MAXX (

        FILTER(ALL(SecurityAmounts[Date]), SecurityAmounts[Date] < MinDate),

        SecurityAmounts[Date]

)