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]))))
daxer-almighty
Solution Sage
Solution SageYou have not stated whether you need a calculated column or a measure... If you need a column, it'll be ALWAYS static. If you want a measure, then it's very easy. You have to create a table with 2 columns. One column will hold run_id and the second will hold all the time instances that are between the start_time and end_time (on the right granularity, that is). The table above will filter the new table using one-to-many, of course. The new table will be hidden. How to see if any(!!!) number of contracts have a non-empty intersection of start-end dates? Well, just see if the time instants that are being mapped to from run_id's have a non-empty intersection. Easy.