Forum Discussion

Jsonify's avatar
Jsonify
Frequent Visitor
5 years ago
Solved

Find overlapping DATETIMES in a table

I am trying to figure out how to approach finding if there is an overlap in DATETIME values. Using the table below as an example:   Run_ID 1 and 2 overlap with each other as well as Run_ID 4 and 5,...
Need Help
Tutorial Requests
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Jsonify 

    is this what you want?

    Column = 
VAR last=maxx(FILTER('Table','Table'[End_Time]<EARLIER('Table'[End_Time])),'Table'[End_Time])
VAR next=MINX(FILTER('Table','Table'[Start_Time]>EARLIER('Table'[Start_Time])),'Table'[Start_Time])
return if(ISBLANK(last)&&next>'Table'[End_Time]||ISBLANK(next)&&last<'Table'[Start_Time],0,if(last>'Table'[Start_Time]||next<'Table'[End_Time],1,0))

  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Jsonify 

    pls try this

    Column = 
var _last=MAXX(FILTER('Table','Table'[Run_ID]<EARLIER('Table'[Run_ID])&&'Table'[Overlaps_A_Run]=0),'Table'[Run_ID])
VAR _next=MINX(FILTER('Table','Table'[Run_ID]>EARLIER('Table'[Run_ID])&&'Table'[Overlaps_A_Run]=0),'Table'[Run_ID])
return if('Table'[Overlaps_A_Run]=0,0,if(ISBLANK(_last),sumx(FILTER(all('Table'),'Table'[Run_ID]<_next),'Table'[Overlaps_A_Run]),if(ISBLANK(_last),sumx(FILTER('Table','Table'[Run_ID]>_last),'Table'[Overlaps_A_Run]),sumx(FILTER('Table','Table'[Run_ID]>_last&&'Table'[Run_ID]<_next),'Table'[Overlaps_A_Run]))))

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
5 years ago

Why bother split each time span to list since what's concerned about is to check whether spans of time overlap. Simple comparison between the End_Time of one run_id and the Start_Time of another run_id; that's enough.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBLCsAgDAWvIlkXNGpq7a4HELoX73+N1k+rEcHVDJMHxggIGzippVbohT+Vep+4wkip0TtA2iLo0SEuE88TU5ypTvOi0aPTXNjibHW2XxvoP91GiMll4nixT+dMk2Lilj6eK1cszT8mVjgPpQc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Run_ID = _t, Start_Time = _t, End_Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Run_ID", Int64.Type}, {"Start_Time", type datetime}, {"End_Time", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Start_Time", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    Overlap = let rs = Table.ToRecords(#"Added Index") in Table.AddColumn(#"Added Index", "Overlap", each Number.From(try [End_Time] > rs{[Index]+1}[Start_Time] or [Start_Time] < rs{[Index]-1}[End_Time] otherwise 0)),
    #"Removed Columns" = Table.RemoveColumns(Overlap,{"Index"})
in
    #"Removed Columns"