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

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.