topic detecting overlapping activities (all occurences) in DAX Commands and Tips

detecting overlapping activities (all occurences)

Anonymous — Mon, 11 Nov 2019 09:05:49 GMT

Hi.

1) I would like to use a DAX formula that can detect a time overlap for the same person, being in two places at once (all occurrences).

2) Moreover, I would also like the formula to detect any overlapping period when the time period crosses over into the following day (row 7, attached screenshot).

3) Finally, is it possible to have a formula that can work through a data set which has not been sorted in chronological order?

Thanks a lot for any help.

Date Person Start_Time End_Time Period Overlap 10-Sep-19 A 06:00:00 18:00:00 yes 10-Sep-19 A 12:00:00 00:00:00 yes 10-Sep-19 B 00:00:00 12:00:00 no 10-Sep-19 B 12:00:00 18:00:00 no 10-Sep-19 B 18:00:00 12:00:00 yes 11-Sep-19 A 00:00:00 12:00:00 no 11-Sep-19 A 12:00:00 00:00:00 yes 11-Sep-19 A 18:00:00 00:00:00 yes 11-Sep-19 B 00:00:00 06:00:00 yes 11-Sep-19 B 12:00:00 00:00:00 no

file

Re: detecting overlapping activities (all occurences)

M_L — Fri, 15 Nov 2019 22:10:27 GMT

Hello,

I would first define the start / end date in this format dd-mm-yy hh:mm:ss with 2 calculated columns :

For end date, I would check if end time is < or > start time to deduce if it is next day or not :

Start_date_full = 'Table'[Date]+'Table'[Start_Time]

End_Date_full = If('Table'[End_Time]>='Table'[Start_Time];'Table'[Date]+'Table'[End_Time];'Table'[Date]+1+'Table'[End_Time])

Then I would reuse these 2 calculated column to deduce overlap yes or no:

Question 1 Period Overlap =

VAR _Person = 'Table'[Person]

VAR _start = 'Table'[Start_date_full]

VAR _end = 'Table'[End_Date_full]

VAR _condition_start_before= CALCULATE(COUNT('Table'[Person]);FILTER('Table';'Table'[Person]=_Person && _start < 'Table'[Start_date_full] && _end > 'Table'[Start_date_full]))

VAR _condition_start_between = CALCULATE(COUNT('Table'[Person]);FILTER('Table';'Table'[Person]=_Person && _start > 'Table'[Start_date_full] && _start < 'Table'[End_Date_full]))

RETURN

If(_condition_start_between + _condition_start_before = 0;"no";"yes")

for you 2nd question, same, I would reuse the 2 first calculated columns

Question 2 overlapping period day = IF(DATEDIFF('Table'[Start_date_full];'Table'[End_Date_full];day)=1;"yes";"no")

Question 3: yes the 2 calculated columns work fine with data set which has not been sorted in chronological order

Let me know if that helps.

Re: detecting overlapping activities (all occurences)

Anonymous — Mon, 18 Nov 2019 12:49:51 GMT

Dear L_M Thank you very much for your reply. Your solution is perfect for what I was trying to do. Unfortunately, I forgot to consider in my sample the scenario where the overlapping calls start at the same time. I have updated the sample data and the Gantt chart introducing one more occurrence that contemplates this other scenario (row 5). I have tried to add a third conditions, _condition_start_same_time, but I can’t manage to make it work. Could you please help me with this as well? Best regard, masG. Date Person Start_Time End_Time Period Overlap 10-Sep-19 A 06:00:00 18:00:00 yes 10-Sep-19 A 12:00:00 00:00:00 yes 10-Sep-19 B 00:00:00 12:00:00 no 10-Sep-19 B 12:00:00 18:00:00 yes 10-Sep-19 B 12:00:00 18:00:00 yes 10-Sep-19 B 18:00:00 12:00:00 yes 11-Sep-19 A 00:00:00 12:00:00 no 11-Sep-19 A 12:00:00 00:00:00 yes 11-Sep-19 A 18:00:00 00:00:00 yes 11-Sep-19 B 00:00:00 06:00:00 yes 11-Sep-19 B 12:00:00 00:00:00 no

Re: detecting overlapping activities (all occurences)

Anonymous — Mon, 18 Nov 2019 12:53:00 GMT

Re: detecting overlapping activities (all occurences)

M_L — Mon, 18 Nov 2019 21:34:43 GMT

The 3rd condition "__condition_start_equal" could be added like below.

Please indicate if this is this fine.

Question 1 Period Overlap = VAR _Person = 'Table'[Person] VAR _start = 'Table'[Start_date_full] VAR _end = 'Table'[End_Date_full] VAR _condition_start_before= CALCULATE(COUNT('Table'[Person]);FILTER('Table';'Table'[Person]=_Person && _start < 'Table'[Start_date_full] && _end >'Table'[Start_date_full])) VAR _condition_start_between = CALCULATE(COUNT('Table'[Person]);FILTER('Table';'Table'[Person]=_Person && _start > 'Table'[Start_date_full] && _start < 'Table'[End_Date_full])) VAR _condition_start_equal = CALCULATE(COUNT('Table'[Person]);FILTER('Table';'Table'[Person]=_Person && _start = 'Table'[Start_date_full] )) VAR __results = If(_condition_start_between + _condition_start_before + _condition_start_equal <= 1 ;"no";"yes") RETURN __results

this one is shorter :

Question 1 Period Overlap short = VAR _Person = 'Table'[Person] VAR _start = 'Table'[Start_date_full] VAR _end = 'Table'[End_Date_full] VAR _condition_overlap = CALCULATE(COUNT('Table'[Person]);FILTER('Table';'Table'[Person]=_Person && ((_start < 'Table'[Start_date_full] && _end >'Table'[Start_date_full]) || _start = 'Table'[Start_date_full] || (_start > 'Table'[Start_date_full] && _start < 'Table'[End_Date_full])))) RETURN If(_condition_overlap <= 1 ;"no";"yes")

Re: detecting overlapping activities (all occurences)

Anonymous — Tue, 19 Nov 2019 12:14:51 GMT

Dear M_L, Your DAX statement is now perfect for my purposes. Thank you so much for your help, @masG

Re: detecting overlapping activities (all occurences)

alessiobayware — Fri, 23 Aug 2024 14:40:35 GMT

Hi, this DAX looks pretty good but by trying to using it, the issue is that it compare with the first row with itself. I tried to add to the filter to exclude the same row that it's trying to compare but after saving it keeps showing "working on it" to the infinity