Earliest date / Latest date with condition

[Anonymous]

Dear community members, 

I have the following situation: 

A person can have multiple of the same therapy session periods. Each therapy period has his own row in power bi (see sample data below). Now I would like to know what the earliest date and the latest date is per client per kind of therapy. But... when the DaysBetweenTherapyPeriods is more than 15 days, it should be seen as two (or more if it happens more often) individual periods. 

Can anyone help me with the formula for the rwo calculated columns? 

Original dataset sample: 

ClientID	Therapy	ClientIDTherapy	StartDate	EndDate	DaysBetweenTherapyPeriods
1001	AAA	1001:AAA	1-1-2021	31-3-2021
1001	AAA	1001:AAA	1-4-2021	10-6-2021	1
1001	AAA	1001:AAA	1-7-2021	15-8-2021	21
1001	AAA	1001:AAA	1-11-2021	31-12-2021	78
1001	AAA	1001:AAA	1-1-2022		1
1001	BBB	1001:BBB	1-1-2021	31-3-2021
1001	BBB	1001:BBB	10-4-2021	30-9-2021	10
1002	AAA	1002:AAA	1-1-2020	31-12-2020
1002	AAA	1002:AAA	1-1-2021	30-11-2021	1
1002	AAA	1002:AAA	1-1-2022		32

How the solution should look like (at least, what I hope to see...) :

ClientID	Therapy	ClientIDTherapy	StartDate	EndDate	DaysBetweenTherapyPeriods	EarliestStartDate	LatestEndDate
1001	AAA	1001:AAA	1-1-2021	31-3-2021		1-1-2021	10-6-2021
1001	AAA	1001:AAA	1-4-2021	10-6-2021	1	1-1-2021	10-6-2021
1001	AAA	1001:AAA	1-7-2021	15-8-2021	21	1-7-2021	15-8-2021
1001	AAA	1001:AAA	1-11-2021	31-12-2021	78	1-11-2021	31-12-2021
1001	AAA	1001:AAA	1-1-2022		1	1-11-2021	31-12-2021
1001	BBB	1001:BBB	1-1-2021	31-3-2021		1-1-2021	30-9-2021
1001	BBB	1001:BBB	10-4-2021	30-9-2021	10	1-1-2021	30-9-2021
1002	AAA	1002:AAA	1-1-2020	31-12-2020		1-1-2020	30-11-2021
1002	AAA	1002:AAA	1-1-2021	30-11-2021	1	1-1-2020	30-11-2021
1002	AAA	1002:AAA	1-1-2022		32	1-1-2022

Hope to hear soon from you.  

Best regards,

Sander

[Anonymous]

HI @Anonymous,

Here are two calculated column formulas that can be used to extract the earliest start date and latest end date, you can try it if helps:

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

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

Notice: I add conditions to prevent the expression to calculate on the date ranges which are not finished because I'm not so sure the processing logic of these parts.

Regards,

Xiaoxin Sheng

[Anonymous]

Dear Xiaoxin, 

It is almost perfect... Until the situation occures that a period of more then 15 days will be followed by a new period (for the same client/product). This new period takes the dates of the wrong previous period. I made a visual and table of the output and also inserted the 'correct' data output in a table. Would it be possible to help me once more with the adjustment in the formulas? 

 Current data output with the new formulas:

ClientID	Therapy	ClientIDTherapy	StartDate	EndDate	DaysBetweenTherapyPeriods	eStartDate	lEndDate
1001	AAA	1001:AAA	1-1-2021	31-3-2021		1-1-2021	30-8-2021
1001	AAA	1001:AAA	1-4-2021	10-6-2021	1	1-1-2021	30-8-2021
1001	AAA	1001:AAA	1-7-2021	15-8-2021	21	1-7-2021	30-8-2021
1001	AAA	1001:AAA	16-8-2021	30-8-2021	1	1-1-2021	30-8-2021
1001	AAA	1001:AAA	1-11-2021	31-12-2021	63	1-11-2021	31-12-2021
1001	AAA	1001:AAA	1-1-2022		1
1001	BBB	1001:BBB	1-1-2021	31-3-2021		1-1-2021	30-9-2021
1001	BBB	1001:BBB	10-4-2021	30-9-2021	10	1-1-2021	30-9-2021
1002	AAA	1002:AAA	1-1-2020	31-12-2020		1-1-2020	30-11-2021
1002	AAA	1002:AAA	1-1-2021	30-11-2021	1	1-1-2020	30-11-2021
1002	AAA	1002:AAA	1-1-2022		32

How the output should be.

ClientID	Therapy	ClientIDTherapy	StartDate	EndDate	DaysBetweenTherapyPeriods	eStartDate	lEndDate
1001	AAA	1001:AAA	1-1-2021	31-3-2021		1-1-2021	10-6-2021
1001	AAA	1001:AAA	1-4-2021	10-6-2021	1	1-1-2021	10-6-2021
1001	AAA	1001:AAA	1-7-2021	15-8-2021	21	1-7-2021	30-8-2021
1001	AAA	1001:AAA	16-8-2021	30-8-2021	1	1-7-2021	30-8-2021
1001	AAA	1001:AAA	1-11-2021	31-12-2021	63	1-11-2021	31-12-2021
1001	AAA	1001:AAA	1-1-2022		1
1001	BBB	1001:BBB	1-1-2021	31-3-2021		1-1-2021	30-9-2021
1001	BBB	1001:BBB	10-4-2021	30-9-2021	10	1-1-2021	30-9-2021
1002	AAA	1002:AAA	1-1-2020	31-12-2020		1-1-2020	30-11-2021
1002	AAA	1002:AAA	1-1-2021	30-11-2021	1	1-1-2020	30-11-2021
1002	AAA	1002:AAA	1-1-2022		32

Hope to hear soon from you!

Best regards, 

Sander Nievergeld

[Anonymous · Accepted solution]

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

[Anonymous]

Dear @Anonymous ,

Thanks for this quick response. This was just the 'little' push I needed. I found a workaround for the highlighted cell issue, so I can continue with my report! 

Many thanks! 

Best regards,

Sander