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]))))
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
Super User
Super Userpls 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]))))Wow. This worked perfectly. Is it too much to ask how your thought process derived this solution?