Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
MrDeg
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:

 

MrDeg_0-1669715926370.png

 

 

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:

 

MrDeg_1-1669715926379.png

 

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.

4 REPLIES 4
leo0403
Frequent Visitor

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?

MrDeg
Frequent Visitor

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

 

BA_Pete
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?

 

Pete



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

Proud to be a Datanaut!




MrDeg
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:

 

MrDeg_0-1669739548123.png

 

 

My function do this:

 

MrDeg_1-1669739548125.png

 

But another acceptable option may be, for example:

 

MrDeg_2-1669739548126.png

 

 

I hope I was helpful and answered your question clearly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors