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]))))
ryan_mayu
Super User
Super Useris 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))
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 |
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
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]))))