Overlapping time ranges

[MrDeg]

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:

let

    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}})

in

    #"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.

[BA_Pete]

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?

Pete

[MrDeg]

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.