help with calculating date difference but only for consecutive series of dates for each client

[Heather]

Greetings Power BI Community,

First, I'd like to express my gratitude for the experts in this community. I'm fairly new to Power BI, yet I've made considerable progress on a report in large part due to the responses you all have provided to others on this forum who were facing challenges similar to mine.

On to the inquiry at hand. I have a dataset that includes start and end dates for patients' enrollments in a program. Sometimes these enrollments are consecutive; sometimes they're not. What I need is for there to be one line per consecutive series of enrollments. If a patient is enrolled three times but not consecutively (like P1 in the example), then the three separate lines for the enrollments should stay. If, however, an enrollment end date is the same as the next enrollment start date, as with P4, then I want the earliest start date and latest end date to be in a single line as one enrollment. From there, I want to calculate the number of days from start to end for enrollments.

I made a preliminary attempt at using the EARLIER command, but ran into trouble when trying to figure out how to a apply it to a scenario where there are more than two series of consecutive start/end dates (e.g., P5). I guess you could could compose something saying, "For all rows belonging to the same patient ID, for consecutive end/start dates (however many that may be), create a DATEDIFF for the earliest applicable start date and latest applicable end date." Or something like that.

Any suggestions on how I can solve this? Thanks in advance for your help!

[v-piga-msft]

Hi @Heather,

What about this formula below?

max date=
CALCULATE (
    MIN ( 'Table1'[End_date] ),
    FILTER (
        'Table1',
        [Num] = EARLIER ( [Num] )
            && 'Table1'[Start_date] = EARLIER ( [End_date] )
    )
)
Column =
IF (
 'Table1'[max date] = BLANK (),
 'Table1'[End_date] - 'Table1'[Start_date],
 CALCULATE (
 MAX ( 'Table1'[max date] ),
 FILTER ( 'Table1', 'Table1'[Num] = EARLIER ( [Num] ) )
 )
 - 'Table1'[Start_date]
)

Best  Regards,

Cherry