Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Overlapping time ranges

Hello community members,


I have a resource that has overlapping activities (an activity: a timerange with a start date and an end date)

I would like to calculate the occupancy time of the resource (Saturation).

Saturation calculates if the resource is occupied, not taking into account the overlapping time.


I would like to know what is the most efficient approach to solve this issue.


I developed a super-complex (at least for my standards) function in power query that do the job using nested functions and recursive loops. The problem is that with big data (few thousands of activities) the execution time probably tends to infinity (I never got to the end).


In this example:





The resource is always occupied but not between 02.00 and 03.00 (day 28).

In this example the Saturation should be:

From 14:00 (day 27) to 02:00 (day 28) +

From 03:00 (day 28) to 08:00 (day 28)



The first gantt is the source, the second is the output of my function:




Duration and CleanedDuration are in minutes.


This is the source table I used in the example:



    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdDBCcAgDAXQVYrngsnXqHSJDiDu0P1PRQLVpNf8Z/zae7gfDmeQiBpBwMHlItJJ0wnNwTgnhaX5o+aw2mQT+dm2bDYJ4CvwomIpe4pFi72v+gbYXlbNEvFbt67NBMlL/YHxAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Activity = _t, StartDate = _t, EndDate = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Activity", type text}, {"StartDate", type datetime}, {"EndDate", type datetime}}),

    #"Inserted Time Subtraction" = Table.AddColumn(#"Changed Type", "Duration", each Duration.TotalMinutes([EndDate] - [StartDate])),

    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Time Subtraction",{{"Duration", Int64.Type}})


    #"Changed Type1"


If needed I can also upload all the super time-expensive nested functions I used to solve the problem.


thank you in advance for your support.

Super User
Super User

Hi @MrDeg ,


What's the relevance of Op2 and Op8 - why are all the workstreams being dumped into these particularly? Is there another layer of grouping that identifies these as the destination for the end-to-end calculation?



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Frequent Visitor

Hello BA_Pete,


thank you for your interest.


Op2 and Op8 are the first (in time) operations before an interruption. They do not have any particular role. The function I developed assigns all the saturation time before an interruption to the first Operation it encounters.


Since my need is to calculate the overall Saturation it’s not a problem how this time is distributed between the operations.


Given this:





My function do this:




But another acceptable option may be, for example:





I hope I was helpful and answered your question clearly.

Helpful resources

Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors