How to count overlapping times given start time and end time?

[polleys]

Introduction:
Hello, thanks for taking the time to view this post.

Dataset:

objectid (Index)	Employee	Date	Start Time	End Time	Overlapping Count (Expected Result)
1	Bob	01/01/2022	9:00 AM	10:00 AM	1
2	Bob	01/01/2022	9:30 AM	10:00 AM	1
3	John	01/05/2022	11:00 AM	12:00 PM	0
4	Sarah	01/10/2022	1:00 PM	5:00 PM	3
5	Sarah	01/10/2022	2:00 PM	5:00 PM	3
6	Sarah	01/10/2022	3:00 PM	5:00 PM	3
7	Sarah	01/14/2022	4:00 PM	5:00 PM	0

Problem / Context:
The table above depicts an employee timesheet. I need to figure out a way to display the count of times an Employee has entered their timesheet with overlapping times on the same Date.

Solutions we've tried:
Currently we are calculating this using the Cartesian Product method. While this method works, our dataset is quite large and takes about an hour to refresh and is only getting slower and slower with more and more data being entered in every day.

I have also tried using lists and while this does work as well it takes even longer than the Cartesian Product method.
https://www.youtube.com/watch?v=MI73nTh34XA

Examples:
Bob is working from 9:00 AM to 10:00 AM on 01/01/2022 as well as from 9:30 AM to 10:00 AM on the same day. Since the times are overlapping on the same day the Overlapping Count column will return 1 to indicate that that row has one time conflict with another row for the same date and employee

All four of Sarah's times overlap with eachother, but because three of her four jobs are on the same day, she would return three overlapping times for 01/10/2022 and zero for 01/14/2022.

Thanks:
If you've made it this far, thank you again for taking the time.

[v-xiaotang]

Hi @polleys 

I just want to confirm if you resolved this question? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

-

And here are some questions need to be confirmed:

(1) I notice that each object of the same employee have the same End Time. So if the end time of the same employee is different, does that mean there is no overlap?

(2) Assuming the following situation exists, what should be the number of overlaps?

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Vera_33]

Hi @polleys 

If Sarah on 01/10/2022 counts 3, then Bob on 01/01/2022 should count 2, right?

If there is a record for Sarah on 01/10/2022 like 9:00 AM to 10:00 AM, then it is not overlapping with other 3 records, right?

If you have large dataset, group by in M can be slow. I did join, and count only 1 as belwo as you can sum up the overlap afterwards, just a thought for your reference

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddCxCoQwDAbgVzkyF0zSVs/bdDwQDhyLg05OJ/j+gxHrKUcCpaThy09JSkDgoF0muZEKOYzM8qhfiI+mk4owl4NLwJb2mvbSeC/zN/N4cqIrnffyc/ggjX5cx/kYIPwNZOQg3ni0OKu8tLhXefXHw8mDyp/3zVzZxh5rXVuLIbT+ruUPGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"objectid (Index)" = _t, Employee = _t, Date = _t, #"Start Time" = _t, #"End Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"objectid (Index)", Int64.Type}, {"Employee", type text}, {"Date", type text}, {"Start Time", type time}, {"End Time", type time}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-US"),
    #"Added Custom1" = Table.AddColumn(#"Changed Type with Locale", "Custom", each List.Times([Start Time], Duration.TotalMinutes([End Time]-[Start Time]),#duration(0,0,1,0))),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Employee", "Date"}, #"Added Custom1", {"Employee", "Date"}, "all", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "overlap", each [
CurIndex=[#"objectid (Index)"],
a=Table.SelectRows([all], each [#"objectid (Index)"]<>CurIndex),
b=if List.Count( List.Intersect({[Custom], List.Combine(a[Custom])}))>1 then 1 else 0
] [b]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom", "all"})
in
    #"Removed Columns"