Forum Discussion
Earliest date / Latest date with condition
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
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
4 Replies
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
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
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