Forum Discussion
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,...
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))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]))))
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_ID | Start_Time | End_Time | Overlaps_A_Run | Runs in Overlap |
| 1 | 7/2/2019 9:00:00 AM | 7/2/2019 5:00:00 PM | 1 | 2 |
| 2 | 7/2/2019 11:00:00 AM | 7/2/2019 9:00:00 PM | 1 | 2 |
| 3 | 7/3/2019 2:00:00 AM | 7/3/2019 8:00:00 AM | 0 | 0 |
| 4 | 7/4/2019 4:00:00 PM | 7/4/2019 11:00:00 PM | 1 | 2 |
| 5 | 7/4/2019 1:00:00 PM | 7/4/2019 7:00:00 PM | 1 | 2 |
| 6 | 7/4/2019 11:30:00 PM | 7/4/2019 11:45:00 PM | 0 | 0 |
| 7 | 7/5/2019 9:00:00 AM | 7/5/2019 9:00:00 PM | 0 | 0 |
