Forum Discussion
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
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
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