Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jsonify
Frequent Visitor

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, but the other do not. I've determined that I belive I'll need to create an extra column so that I can SUM the overlap instances for a future measure. 

 

Run_IDStart_TimeEnd_TimeOverlaps_A_Run
17/2/2019 9:00:00 AM7/2/2019 5:00:00 PM1
27/2/2019 11:00:00 AM7/2/2019 9:00:00 PM1
37/3/2019 2:00:00 AM7/3/2019 8:00:00 AM0
47/4/2019 4:00:00 PM7/4/2019 11:00:00 PM1
57/4/2019 1:00:00 PM7/4/2019 7:00:00 PM1
67/4/2019 11:30:00 PM 7/4/2019 11:45:00 PM 0
77/5/2019 9:00:00 AM 7/5/2019 9:00:00 PM 0

 

I've found a way to do this when dealing with the DATE only type following this tutorial: https://www.youtube.com/watch?v=SfcHsB6uWjE,  but it doesn't seem to work the same for DATETIME.

 

Any ideas here?

2 ACCEPTED SOLUTIONS
ryan_mayu
Super User
Super User

@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))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@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]))))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

11 REPLIES 11
CNENFRNL
Community Champion
Community Champion

Why bother split each time span to list since what's concerned about is to check whether spans of time overlap. Simple comparison between the End_Time of one run_id and the Start_Time of another run_id; that's enough.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBLCsAgDAWvIlkXNGpq7a4HELoX73+N1k+rEcHVDJMHxggIGzippVbohT+Vep+4wkip0TtA2iLo0SEuE88TU5ypTvOi0aPTXNjibHW2XxvoP91GiMll4nixT+dMk2Lilj6eK1cszT8mVjgPpQc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Run_ID = _t, Start_Time = _t, End_Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Run_ID", Int64.Type}, {"Start_Time", type datetime}, {"End_Time", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Start_Time", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    Overlap = let rs = Table.ToRecords(#"Added Index") in Table.AddColumn(#"Added Index", "Overlap", each Number.From(try [End_Time] > rs{[Index]+1}[Start_Time] or [Start_Time] < rs{[Index]-1}[End_Time] otherwise 0)),
    #"Removed Columns" = Table.RemoveColumns(Overlap,{"Index"})
in
    #"Removed Columns"

Screenshot 2021-08-21 190032.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

ryan_mayu
Super User
Super User

@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))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

@Jsonify 

could you pls explain more about the your logic? why we get 2?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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?

@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]))))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Wow. This worked perfectly. Is it too much to ask how your thought process derived this solution?

@Jsonify 

why not just sum(Overlaps_A_Run), the reslut is 4 overlap runs.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I am trying to sum just the "series" of runs that overlapped each other, not the entire column. In other words, Run 1 and Run 2 are considered a series because they were involved in an overlap with each other. And in that series there were only 2. In other series there could be a lot more and I'd like to be able to capture the sum of that series.

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. 

daxer-almighty
Solution Sage
Solution Sage

You 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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.