cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
masG Frequent Visitor
Frequent Visitor

detecting overlapping activities (all occurences)

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 

 

chart.pngtable + Gantt chart

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
M_L Frequent Visitor
Frequent Visitor

Re: detecting overlapping activities (all occurences)

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

 

 

View solution in original post

5 REPLIES 5
M_L Frequent Visitor
Frequent Visitor

Re: detecting overlapping activities (all occurences)

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. 

 
masG Frequent Visitor
Frequent Visitor

Re: detecting overlapping activities (all occurences)

 

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
masG Frequent Visitor
Frequent Visitor

Re: detecting overlapping activities (all occurences)

new_Gantt.pngnew Gantt chart

Highlighted
M_L Frequent Visitor
Frequent Visitor

Re: detecting overlapping activities (all occurences)

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

 

 

View solution in original post

masG Frequent Visitor
Frequent Visitor

Re: detecting overlapping activities (all occurences)

Dear @M_L,

Your DAX statement is now perfect for my purposes.

Thank you so much for your help,
@masG

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)