How to calculate days between gaps in continuous dates?

[ClemFandango]

Hello wonderful PowerBI community!

First off, apologies that the data is difficult to read, so i have copied both a screenshot and data table of the same data to help illustrate. I have spent hours reading every help post I can find, but the table continually loses its formatting. I am also unable to upload my PBix. Anyway here is my real issue...

I am trying to find the gaps between continuous dates and then calculate the number of days (for specific references as per Ref column) for how long there was a gap between a continuous date. This may or may not be complicated by some of the start & end dates overlapping.

My data is as per below - I am hoping to calculate the gaps by adding the last 3 calculated columns. The columns are blank where there is a continuous date and no prior end date (to the continuous date). Any ideas in how I can get these 3 calculated columns to work?

End Date Prior to continuous dates = the end date of the previous block of continuous dates (for each Ref)

Earliest Start Date of continuous dates = the min start date where there is a date for 'End Date Prior to continuous dates' (for each Ref)

Days between Start&End = differeance between 'End Date Prior to continuous dates' & 'Earliest Start Date of continuous dates' in days

Ref	End Date	Start Date	End Date prior to continous dates	Earliest Start Date of continuous dates	Days between Start&End
126	30/04/2024	28/03/2023	30/04/2022	28/03/2023	332
126	30/04/2024	28/03/2023	30/04/2022	28/03/2023	332
126	30/04/2021	30/04/2020
126	29/04/2019	01/01/2018
126	30/04/2020	30/04/2019
126	30/04/2022	30/04/2021
174	31/10/2021	31/10/2020
174	31/10/2019	01/11/2018
174	31/10/2020	31/10/2019
174	31/10/2023	08/08/2022	31/10/2021	08/08/2022	281
174	31/10/2023	08/08/2022	31/10/2021	08/08/2022	281
174	31/10/2021	31/10/2020
174	01/11/2020	31/05/2019
174	30/05/2019	01/01/2018
376	30/04/2021	30/04/2020
376	29/04/2019	01/01/2018
376	30/04/2020	30/04/2019
376	30/09/2023	30/09/2022	30/04/2021	01/02/2022	277
376	30/09/2023	30/09/2022	30/04/2021	01/02/2022	277
376	30/09/2022	01/02/2022	30/04/2021	01/02/2022	277
376	30/09/2022	01/02/2022	30/04/2021	01/02/2022	277
1421	29/02/2024	28/02/2023
1421	31/03/2024	31/03/2023
1421	29/02/2024	28/02/2023
1421	31/03/2024	31/03/2023
1421	29/02/2024	28/02/2023
1421	31/03/2024	31/03/2023
1421	31/03/2021	30/06/2019
1421	31/03/2022	31/03/2021
1421	31/03/2023	31/03/2022
1421	31/03/2021	31/03/2020
1421	31/03/2020	31/03/2019
1421	31/03/2022	31/03/2021
1421	31/03/2020	31/03/2019
1421	31/03/2023	31/03/2022
1421	31/03/2021	31/03/2020
1421	31/03/2022	31/03/2021
1421	31/03/2020	31/03/2019
1421	31/03/2023	31/03/2022
1421	31/03/2019	01/01/2018
1421	31/03/2019	01/01/2018
1421	29/06/2019	01/01/2018

[ClemFandango]

Hi @Wilson_ thanks again for this. I sovled this by adding a couple of extra conditions to reduce the false positivies that resulted from the overlapping dates.

[Wilson_]

Very cool, glad you figured it out. 😄

I meant to take another look at this but never got around to it hah.

[ClemFandango]

Hi @Wilson_ 

Apologies, i thought i marked this as a solution already:-

Just another question, i have just found an issue with my logic. I am trying to return the start & end date where there is a break with a non-continuous date. Your calculated column works well in the vast majority of cases, however where the 'Start Date' & 'End Date' overlap previously existing dates, it will falsely report a break in continuous dates.

Please see example of the issue below. Do you have any ideas how I can tweak your amazing calculated column to ignore situations like this? Ideally this will show no Continuous Dates in the example below.

I have highlighted a specific date in green. The yellow dates show where it overlaps.

(more useable version of the screenshot above)

Ref	Start Date	End Date	End Date Prior to Continuous Dates	Earliest Start Date of Continuous Dates	Days Between Start&End
442	01/01/2018	29/09/2019
442	01/01/2018	29/09/2019
442	01/01/2018	29/09/2019
442	01/01/2018	29/09/2019
442	01/01/2018	29/09/2019
442	01/01/2018	29/09/2019
442	30/09/2019	30/09/2020
442	30/09/2019	30/09/2020
442	30/09/2019	30/09/2020
442	30/09/2019	30/09/2020
442	30/09/2019	30/09/2020
442	30/09/2019	30/09/2020
442	21/07/2020	30/08/2021	29/09/2019	21/07/2020	296
442	30/09/2020	30/09/2021
442	30/09/2020	30/09/2021
442	30/09/2020	30/09/2021
442	30/09/2020	30/09/2021
442	02/10/2020	30/09/2021	30/09/2020	02/10/2020	2
442	31/08/2021	31/08/2022
442	30/09/2021	30/09/2022
442	30/09/2021	30/09/2022
442	30/09/2021	30/09/2022
442	30/09/2021	30/09/2022
442	30/09/2021	30/09/2022
442	11/10/2021	13/10/2021	30/09/2021	11/10/2021	11
442	30/09/2022	30/09/2023
442	30/09/2022	30/09/2023
442	30/09/2022	30/09/2023
442	30/09/2022	30/09/2023
442	30/09/2022	30/09/2023

[Wilson_ · Accepted solution]

Hello ClemFandango,

Try the below for your calculated columns:

End Date Prior to Continuous Dates = 
VAR Ref = 'Table'[Ref]
VAR StartDate = 'Table'[Start Date]
VAR Filtered =
FILTER (
    'Table',
    'Table'[Ref] = Ref &&
    'Table'[End Date] <= StartDate
)

VAR Result =
MAXX (
    Filtered,
    'Table'[End Date]
)

RETURN IF ( StartDate - Result > 1, Result )

Earliest Start Date of Continuous Dates = 
IF (
    NOT ( ISBLANK ( 'Table'[End Date Prior to Continuous Dates] ) ),
    'Table'[Start Date]
)

Days Between Start&End = 'Table'[Earliest Start Date of Continuous Dates] - 'Table'[End Date Prior to Continuous Dates]

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[ClemFandango]

@Wilson_ This is amazing! Thank you so much

[Wilson_]

Sweet, glad it worked for you!

Can you please mark my last response as the solution so people know this one is solved and to make the solution easier to find for anybody else who sees this post in the future? 😄