Earliest date / Latest date with condition

HI Anonymous,

You can try to use the new calculated column formulas, I add the 'previous date' and the 'last date' variable to prevent the expression calculated on wrong ranges.

eStartDate = 
VAR prevDate =
    CALCULATE (
        MAX ( 'Table'[StartDate] ),
        FILTER (
            'Table',
            [StartDate] <= EARLIER ( 'Table'[StartDate] )
                && 'Table'[ClientIDTherapy] = EARLIER ( 'Table'[ClientIDTherapy] )
                && [DaysBetweenTherapyPeriods] >= 15
        )
    )
VAR _start =
    CALCULATE (
        MIN ( 'Table'[StartDate] ),
        FILTER (
            'Table',
            [StartDate] <= EARLIER ( 'Table'[StartDate] )
                && [StartDate] >= prevDate
                && 'Table'[ClientIDTherapy] = EARLIER ( 'Table'[ClientIDTherapy] )
                && [DaysBetweenTherapyPeriods] < 15
        )
    )
RETURN
    IF (
        [StartDate] <> BLANK ()
            && [EndDate] <> BLANK (),
        IF ( 'Table'[DaysBetweenTherapyPeriods] > 15, [StartDate], _start )
    )

lEndDate = 
VAR nextDate =
    CALCULATE (
        MIN ( 'Table'[EndDate] ),
        FILTER (
            'Table',
            [EndDate] > EARLIER ( 'Table'[EndDate] )
                && 'Table'[ClientIDTherapy] = EARLIER ( 'Table'[ClientIDTherapy] )
                && [DaysBetweenTherapyPeriods] >= 15
        )
    )
VAR _lastDate =
    CALCULATE (
        MAX ( 'Table'[EndDate] ),
        FILTER (
            'Table',
            [EndDate] >= EARLIER ( 'Table'[EndDate] )
                && 'Table'[ClientIDTherapy] = EARLIER ( 'Table'[ClientIDTherapy] )
        )
    )
VAR _end =
    CALCULATE (
        MAX ( 'Table'[EndDate] ),
        FILTER (
            'Table',
            [EndDate] >= EARLIER ( 'Table'[EndDate] )
                && [EndDate] <= nextDate
                && 'Table'[ClientIDTherapy] = EARLIER ( 'Table'[ClientIDTherapy] )
                && [DaysBetweenTherapyPeriods] < 15
        )
    )
RETURN
    IF (
        [StartDate] <> BLANK ()
            && [EndDate] <> BLANK (),
        IF ( _end <> BLANK (), _end, IF ( nextDate <> BLANK (), [EndDate], _lastDate ) )
    )

Notice: the highlight cell result should be 08/16/2021 instead of 07/01/2021 or they will violate the 15-day condition.

Regards,

Xiaoxin Sheng