Overlapping time ranges

[Syndicate_Admin]

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.

[Syndicate_Admin]

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

[Syndicate_Admin]

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.

[Syndicate_Admin]

Hi, did you find out a better way to handle with this kind of problem? I'm struggling on a similar problem, can you share your code?

[Syndicate_Admin]

Unfortunately not, 

some consultants suggested that this kind of transformation cannot be managed in PowerQuery-Dax.

However this was my work:

Source

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdDBDcAgCAXQVRrPJuJX1HaJDmDcofufGmtSBa+fJwK1mvvxxhp2yA4EHD5dRCMpI6EeNNspJI0/FY+HDbLCmy3TRlEB9Ah+UpbUa4pJk/wv6wmwbJZFE9Zdl1mLKAQtlwuc+w5r8F2vtRc=", 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"

Output

let
    CleanOverlaps = fn_CleanOverlaps(Source,"Activity","StartDate","EndDate"),
    #"Merged Queries" = Table.NestedJoin(Source, {"Activity"}, CleanOverlaps, {"Activity"}, "CleanOverlaps", JoinKind.LeftOuter),
    #"Expanded CleanOverlaps" = Table.ExpandTableColumn(#"Merged Queries", "CleanOverlaps", {"CleanedDuration"}, {"CleanedDuration"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded CleanOverlaps",null,0,Replacer.ReplaceValue,{"CleanedDuration"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "OverlapDuration", each [Duration] - [CleanedDuration], type number),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Subtraction",{{"CleanedDuration", Int64.Type}, {"OverlapDuration", Int64.Type}})
in
    #"Changed Type"

This is the sequence of nested functions I developed, in the mean time I forgot how they work.

if you find a way to improve them please let me know, the funny thing is that I was able to do much more advanced things in VBA 🤔 then PowerQuery-DAX:

fn_CleanOverlaps

(Tab as table,  KeyCol_S as text, StartD_S as text, EndD_S as text ) =>
let
    MaxLoop = Table.RowCount(Tab),
    SortedTab = Table.Sort(Tab,{{StartD_S,Order.Ascending},{EndD_S,Order.Ascending}}),
    OverlapsChanged = #"private fn_CleanOverlaps (loop)"(SortedTab,MaxLoop - 1,-1,{},KeyCol_S,StartD_S,EndD_S),
    RemoveSkips = Table.SelectRows(OverlapsChanged[Tab], each not List.Contains(OverlapsChanged[Skip], [Activity])),
    AddDuration = Table.AddColumn(RemoveSkips, "CleanedDuration", each Duration.TotalMinutes([EndDate] - [StartDate]))

in

    AddDuration

private fn_CleanOverlaps (loop)

(Tab as table, MaxLoop as number, StartLoop as number, SkipList as list,KeyCol_S as text, StartD_S as text, EndD_S as text ) =>
let
    CurrentLoop = StartLoop + 1,
    LoopTab = #"private fn_CleanOverlaps (Single Record work)"(Tab, CurrentLoop,SkipList,KeyCol_S,StartD_S,EndD_S),
    Output = if CurrentLoop >= MaxLoop then
            LoopTab
        else
           @#"private fn_CleanOverlaps (loop)"(LoopTab[Tab],MaxLoop, CurrentLoop,LoopTab[Skip],KeyCol_S,StartD_S,EndD_S)

in
    Output

private fn_CleanOverlaps (Single Record work)

(Tab as table, n as number,SkipList as list, KeyCol_S as text, StartD_S as text, EndD_S as text ) =>
let
    MyRecord = Tab{n},//Target Record
    MyKey = Record.Field(MyRecord,KeyCol_S),//Target Key
    DeleteOtherRows = Table.SelectColumns(Tab,{KeyCol_S,StartD_S,EndD_S}),
    A_End = Record.Field(MyRecord,EndD_S),

    RemoveMyRecord = Table.SelectRows(DeleteOtherRows, each (Record.Field(_,KeyCol_S) <> MyKey)),//Remove Record

    ModRecord = #"private fn_CleanOverlaps (modify A Record)"(RemoveMyRecord,MyRecord,KeyCol_S,StartD_S,EndD_S),
    Mod_AStart = Record.Field(ModRecord,StartD_S),
    Mod_AEnd = Record.Field(ModRecord,EndD_S),

    Mod_TF =    if A_End <> Mod_AEnd then
                    true  
                else  
                    false,

    
    AddRelation = Table.AddColumn(RemoveMyRecord, "Relation", each fn_TimeRangeRelation(Mod_AStart,Mod_AEnd,Record.Field(_,StartD_S),Record.Field(_,EndD_S))),

    nSkipList = Table.ToList( Table.SelectColumns( Table.SelectRows(AddRelation, each not List.Contains({"NoRelation","BcontainsA"}, [Relation])),{KeyCol_S})),

    SkipList =  if List.IsEmpty(SkipList) then  
                    nSkipList  
                else  
                    List.Combine({SkipList,nSkipList}),

    DeleteRelCol = Table.RemoveColumns(AddRelation,{"Relation"}),
    AddNewRecord = Table.InsertRows(DeleteRelCol,n,{ModRecord})

in
    [
    IntialRecord = MyRecord,
    Tab = AddNewRecord,
    Skip = SkipList,
    n = n]

private fn_CleanOverlaps (modify A Record) 

(Tab as table, MyRecord as record, KeyCol_S as text, StartD_S as text, EndD_S as text ) =>
let

    A_Key = Record.Field(MyRecord,KeyCol_S),
    A_Start = Record.Field(MyRecord,StartD_S),
    A_End = Record.Field(MyRecord,EndD_S),

    AddRelation = Table.AddColumn(Tab, "Relation", each fn_TimeRangeRelation(A_Start,A_End,Record.Field(_,StartD_S),Record.Field(_,EndD_S))),
    AB_R = List.PositionOf(AddRelation[Relation], "AB_Intersect"),

    ModRecord = if AB_R <> -1 then
                #"private fn_CleanOverlaps (modify A Record)"(Tab, 
                                            Record.FromList( {A_Key, A_Start, Record.Field(AddRelation{AB_R},EndD_S)},{KeyCol_S, StartD_S, EndD_S}), 
                                            KeyCol_S, StartD_S, EndD_S)
                else  
                    MyRecord

in
    ModRecord

fn_TimeRangeRelation

= (A_start as datetime,A_end as datetime ,B_start as datetime, B_end as datetime) as text =>
/*
OutputType options
    "RelationTF"
    "RelType"
    "OverlapTime"
*/

let
    //OutputType = if OutputType is null then OutputType = "RelType" else OutputType,
    A_Duration = Duration.TotalHours(A_end-A_start),
    B_Duration = Duration.TotalHours(B_end-B_start),
    AStart_BEnd = Duration.TotalHours(A_start-B_end),
    BStart_AEnd = Duration.TotalHours(B_start-A_end),
    GetRelation = if A_Duration >= 0 and B_Duration >=0 then
                    if AStart_BEnd >= 0 or BStart_AEnd >= 0 then
                     "NoRelation"
                    else let 
                            StartRelation = Duration.TotalHours(A_start-B_start),
                            EndRelation = Duration.TotalHours(A_end - B_end),
                            RelType = if StartRelation > 0 then //Future
                                        if EndRelation > 0 then // Future
                                            "BA_Intersect"
                                        else if EndRelation < 0 then // Past
                                            "BcontainsA"
                                        else
                                            "BcontainsA"
                                      else if StartRelation < 0 then //Past
                                        if EndRelation > 0 then // Future
                                            "AcontainsB"
                                        else if EndRelation < 0 then // Past
                                            "AB_Intersect"
                                        else
                                            "AcontainsB"   
                                      else  // equal
                                        if EndRelation > 0 then // Future
                                            "AcontainsB"
                                        else if EndRelation < 0 then // Past
                                            "BcontainsA"
                                        else
                                            "AB_equal"  
                         in 
                         RelType
                else "Invalid Ranges"

in

    GetRelation