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, but the other do not. I've determined that I belive I'll need to create an extra column so that I can SUM the overlap instances for a future measure. 

 

Run_IDStart_TimeEnd_TimeOverlaps_A_Run
17/2/2019 9:00:00 AM7/2/2019 5:00:00 PM1
27/2/2019 11:00:00 AM7/2/2019 9:00:00 PM1
37/3/2019 2:00:00 AM7/3/2019 8:00:00 AM0
47/4/2019 4:00:00 PM7/4/2019 11:00:00 PM1
57/4/2019 1:00:00 PM7/4/2019 7:00:00 PM1
67/4/2019 11:30:00 PM 7/4/2019 11:45:00 PM 0
77/5/2019 9:00:00 AM 7/5/2019 9:00:00 PM 0

 

I've found a way to do this when dealing with the DATE only type following this tutorial: https://www.youtube.com/watch?v=SfcHsB6uWjE,  but it doesn't seem to work the same for DATETIME.

 

Any ideas here?

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

11 Replies

  • 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"

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    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))

    • Jsonify's avatar
      Jsonify
      Frequent Visitor
      5 years ago

      This seems to have worked great. As somewhat of a new user to DAX and Power BI, I now need to break down your solution to better understand what each of the pieces are doing, to demystify it for myself. 

    • Jsonify's avatar
      Jsonify
      Frequent Visitor
      5 years ago

      I've been trying to think of a way to capture the number of runs that occurred in the overlap like the example below. But I'm having trouble figuring out how to add up just the occurrances during the "last" through "next" range. Is that even possible?

       

      Run_IDStart_TimeEnd_Time

      Overlaps_A_Run

      Runs in Overlap

      17/2/2019 9:00:00 AM7/2/2019 5:00:00 PM12
      27/2/2019 11:00:00 AM7/2/2019 9:00:00 PM12
      37/3/2019 2:00:00 AM7/3/2019 8:00:00 AM00
      47/4/2019 4:00:00 PM7/4/2019 11:00:00 PM12
      57/4/2019 1:00:00 PM7/4/2019 7:00:00 PM12
      67/4/2019 11:30:00 PM 7/4/2019 11:45:00 PM 00
      77/5/2019 9:00:00 AM 7/5/2019 9:00:00 PM 00
  • daxer-almighty's avatar
    daxer-almighty
    Icon for Solution Sage rankSolution Sage
    5 years ago

    You have not stated whether you need a calculated column or a measure... If you need a column, it'll be ALWAYS static. If you want a measure, then it's very easy. You have to create a table with 2 columns. One column will hold run_id and the second will hold all the time instances that are between the start_time and end_time (on the right granularity, that is). The table above will filter the new table using one-to-many, of course. The new table will be hidden. How to see if any(!!!) number of contracts have a non-empty intersection of start-end dates? Well, just see if the time instants that are being mapped to from run_id's have a non-empty intersection. Easy.