Find overlapping DATETIMES in a table

[Jsonify]

I am trying to figure out how to approach finding if there is an overlap in DATETIME values. Using the table below as an example:

Run_ID 1 and 2 overlap with each other as well as Run_ID 4 and 5, but the other do not. I've determined that I belive I'll need to create an extra column so that I can SUM the overlap instances for a future measure. 

Run_ID	Start_Time	End_Time	Overlaps_A_Run
1	7/2/2019 9:00:00 AM	7/2/2019 5:00:00 PM	1
2	7/2/2019 11:00:00 AM	7/2/2019 9:00:00 PM	1
3	7/3/2019 2:00:00 AM	7/3/2019 8:00:00 AM	0
4	7/4/2019 4:00:00 PM	7/4/2019 11:00:00 PM	1
5	7/4/2019 1:00:00 PM	7/4/2019 7:00:00 PM	1
6	7/4/2019 11:30:00 PM	7/4/2019 11:45:00 PM	0
7	7/5/2019 9:00:00 AM	7/5/2019 9:00:00 PM	0

I've found a way to do this when dealing with the DATE only type following this tutorial: https://www.youtube.com/watch?v=SfcHsB6uWjE,  but it doesn't seem to work the same for DATETIME.

Any ideas here?

[ryan_mayu · Accepted solution]

@Jsonify 

is this what you want?

Column = 
VAR last=maxx(FILTER('Table','Table'[End_Time]<EARLIER('Table'[End_Time])),'Table'[End_Time])
VAR next=MINX(FILTER('Table','Table'[Start_Time]>EARLIER('Table'[Start_Time])),'Table'[Start_Time])
return if(ISBLANK(last)&&next>'Table'[End_Time]||ISBLANK(next)&&last<'Table'[Start_Time],0,if(last>'Table'[Start_Time]||next<'Table'[End_Time],1,0))

[CNENFRNL]

Why bother split each time span to list since what's concerned about is to check whether spans of time overlap. Simple comparison between the End_Time of one run_id and the Start_Time of another run_id; that's enough.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBLCsAgDAWvIlkXNGpq7a4HELoX73+N1k+rEcHVDJMHxggIGzippVbohT+Vep+4wkip0TtA2iLo0SEuE88TU5ypTvOi0aPTXNjibHW2XxvoP91GiMll4nixT+dMk2Lilj6eK1cszT8mVjgPpQc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Run_ID = _t, Start_Time = _t, End_Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Run_ID", Int64.Type}, {"Start_Time", type datetime}, {"End_Time", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Start_Time", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    Overlap = let rs = Table.ToRecords(#"Added Index") in Table.AddColumn(#"Added Index", "Overlap", each Number.From(try [End_Time] > rs{[Index]+1}[Start_Time] or [Start_Time] < rs{[Index]-1}[End_Time] otherwise 0)),
    #"Removed Columns" = Table.RemoveColumns(Overlap,{"Index"})
in
    #"Removed Columns"

[ryan_mayu · Accepted solution]

@Jsonify 

is this what you want?

Column = 
VAR last=maxx(FILTER('Table','Table'[End_Time]<EARLIER('Table'[End_Time])),'Table'[End_Time])
VAR next=MINX(FILTER('Table','Table'[Start_Time]>EARLIER('Table'[Start_Time])),'Table'[Start_Time])
return if(ISBLANK(last)&&next>'Table'[End_Time]||ISBLANK(next)&&last<'Table'[Start_Time],0,if(last>'Table'[Start_Time]||next<'Table'[End_Time],1,0))

[Jsonify]

I've been trying to think of a way to capture the number of runs that occurred in the overlap like the example below. But I'm having trouble figuring out how to add up just the occurrances during the "last" through "next" range. Is that even possible?

Run_ID	Start_Time	End_Time	Overlaps_A_Run	Runs in Overlap
1	7/2/2019 9:00:00 AM	7/2/2019 5:00:00 PM	1	2
2	7/2/2019 11:00:00 AM	7/2/2019 9:00:00 PM	1	2
3	7/3/2019 2:00:00 AM	7/3/2019 8:00:00 AM	0	0
4	7/4/2019 4:00:00 PM	7/4/2019 11:00:00 PM	1	2
5	7/4/2019 1:00:00 PM	7/4/2019 7:00:00 PM	1	2
6	7/4/2019 11:30:00 PM	7/4/2019 11:45:00 PM	0	0
7	7/5/2019 9:00:00 AM	7/5/2019 9:00:00 PM	0	0

[ryan_mayu]

@Jsonify 

could you pls explain more about the your logic? why we get 2?

[Jsonify]

In the example table, Run 1 and 2 are overlapping in their run timedate and I'm trying to figure out how I can record how many runs were involved in the overlap. So for this case there were 2. And the same thing for Run 4 and 5, there were 2 runs involved. Does that make sense?

[ryan_mayu · Accepted solution]

@Jsonify 

pls try this

Column = 
var _last=MAXX(FILTER('Table','Table'[Run_ID]<EARLIER('Table'[Run_ID])&&'Table'[Overlaps_A_Run]=0),'Table'[Run_ID])
VAR _next=MINX(FILTER('Table','Table'[Run_ID]>EARLIER('Table'[Run_ID])&&'Table'[Overlaps_A_Run]=0),'Table'[Run_ID])
return if('Table'[Overlaps_A_Run]=0,0,if(ISBLANK(_last),sumx(FILTER(all('Table'),'Table'[Run_ID]<_next),'Table'[Overlaps_A_Run]),if(ISBLANK(_last),sumx(FILTER('Table','Table'[Run_ID]>_last),'Table'[Overlaps_A_Run]),sumx(FILTER('Table','Table'[Run_ID]>_last&&'Table'[Run_ID]<_next),'Table'[Overlaps_A_Run]))))

[Jsonify]

Wow. This worked perfectly. Is it too much to ask how your thought process derived this solution?

[ryan_mayu]

@Jsonify 

why not just sum(Overlaps_A_Run), the reslut is 4 overlap runs.

[Jsonify]

I am trying to sum just the "series" of runs that overlapped each other, not the entire column. In other words, Run 1 and Run 2 are considered a series because they were involved in an overlap with each other. And in that series there were only 2. In other series there could be a lot more and I'd like to be able to capture the sum of that series.

[Jsonify]

This seems to have worked great. As somewhat of a new user to DAX and Power BI, I now need to break down your solution to better understand what each of the pieces are doing, to demystify it for myself. 

[daxer-almighty]

You have not stated whether you need a calculated column or a measure... If you need a column, it'll be ALWAYS static. If you want a measure, then it's very easy. You have to create a table with 2 columns. One column will hold run_id and the second will hold all the time instances that are between the start_time and end_time (on the right granularity, that is). The table above will filter the new table using one-to-many, of course. The new table will be hidden. How to see if any(!!!) number of contracts have a non-empty intersection of start-end dates? Well, just see if the time instants that are being mapped to from run_id's have a non-empty intersection. Easy.