Anonymous

Not applicable

4 years ago

Solved

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)....

Need Help

Anonymous

4 years ago

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

Not applicable

4 years ago

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

Not applicable

4 years ago

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

Not applicable

4 years ago

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
Not applicable
4 years ago
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

Forum Discussion

Earliest date / Latest date with condition

Related content

Japanese UI: Earliest/Latest date labels reversed

Display latest data compared to earliest data

Earliest and latest date

Summaries table with Earliest date / Latest date with condition

earliest and latest dates