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

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
ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
5 years ago

Jsonify 

could you pls explain more about the your logic? why we get 2?

  • Jsonify's avatar
    Jsonify
    Frequent Visitor
    5 years ago

    In the example table, Run 1 and 2 are overlapping in their run timedate and I'm trying to figure out how I can record how many runs were involved in the overlap. So for this case there were 2. And the same thing for Run 4 and 5, there were 2 runs involved. Does that make sense?

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

      • Jsonify's avatar
        Jsonify
        Frequent Visitor
        5 years ago

        Wow. This worked perfectly. Is it too much to ask how your thought process derived this solution?

    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      5 years ago

      Jsonify 

      why not just sum(Overlaps_A_Run), the reslut is 4 overlap runs.

      • Jsonify's avatar
        Jsonify
        Frequent Visitor
        5 years ago

        I am trying to sum just the "series" of runs that overlapped each other, not the entire column. In other words, Run 1 and Run 2 are considered a series because they were involved in an overlap with each other. And in that series there were only 2. In other series there could be a lot more and I'd like to be able to capture the sum of that series.