Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 detec...
  • M_L's avatar
    M_L
    6 years ago

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

     

     

M_L's avatar
M_L
Frequent Visitor
6 years ago

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. 

 
Anonymous's avatar
Anonymous
Not applicable
6 years ago

 

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
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    new Gantt chart

    • M_L's avatar
      M_L
      Frequent Visitor
      6 years ago

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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        Dear M_L,

Your DAX statement is now perfect for my purposes.

Thank you so much for your help,
@masG