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
Super 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
    • ryan_mayu's avatar
      ryan_mayu
      Super 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?