Properly summing up time spent in overlapping appointments

[Julian1]

I'm building a report that shows how much time people spend in appointments on their calendar. I run into a challenge when I'm dealing with overlapping appointments. As an example, I have a list of five appointments shown below using a table visualization that provides a sum of the hours at the bottom. The problem is, that sum is wrong. The start time of the very first meeting is 11am and the end time of the last meeting is 2pm (3 hours total). However, the sum of the durations is 3.5 hours. 

The reason for this is that two of the appointments overlap. If you look at the last row (1-2pm appointment) and the one right before it (1-1:30PM), you will see that they overlap. It's not possible to attend 1.5 hours of meetings between 1pm and 2pm, but that's the result that is showing. What's the best approach to account for this overlap so the total duration is 3 hours and not 3.5? 

[amitchandak]

@Julian1 , I create an overlapping logic file, a few days back. See if this can help

https://www.dropbox.com/s/1mlq21o1xjuw8il/overlappingdates.pbix?dl=0

[Greg_Deckler]

@Julian1 - I did it like this, you will need an Index column, at least it makes it much easier. See attached PBIX below sig. Table 13, Page 13

Duration = 
    VAR __Current = MAX([Index])
    VAR __Start = MAX ([Start])
    VAR __End = MAX([End])
    VAR __Duration = DATEDIFF(__Start,__End,MINUTE)/60
    VAR __Table = ADDCOLUMNS(FILTER(ALL('Table (13)'),[Index]<>__Current),"__Contains",IF(__Start>=[Start]&&__End<=[End],1,0))
RETURN
    IF(MAXX(__Table,[__Contains])=1,0,__Duration)


DurationTotal = 
    IF(HASONEVALUE('Table (13)'[Index]),[Duration],
        SUMX(ADDCOLUMNS(SUMMARIZE('Table (13)',[Index]),"__Duration",[Duration]),[__Duration]))

[Julian1]

Thanks, Greg. That was super helpful, thanks so much for providing an examlpe! I'm new at this and spent my day playing with it and learning DAX Studio and learned a lot! 

I did have a couple of questions, though, if you don't mind. I noticed when I changed the data a bit I found some spots where the formula seemed to produce unexpected values. I didn't change your formula at all, just the data in the table. Would you mind letting me know what I'm doing wrong? I think I'm entering the data wrong in some way. 

If I have meetings that partially overlap, it shows 2 hours of time even though there's only 1.5 hours between 11am and 12:30 PM (I was hoping for 1.5 hours of total meeting time)

And two meetings that overlap exactly show zero time (I had hoped for 1 hour of meeting time): 

Am I putting the data in wrong? 

[Greg_Deckler]

@Julian1 - Here you go, I think this fixes it:

Duration = 
    VAR __Current = MAX([Index])
    VAR __Start = MAX ([Start])
    VAR __End = MAX([End])
    VAR __Duration = DATEDIFF(__Start,__End,MINUTE)/60
    VAR __Table = 
        ADDCOLUMNS(
            FILTER(ALL('Table (13)'),[Index]<>__Current),
            "__Contains",IF(__Start=[Start] && __End=[End] && [Index]>__Current ,0,IF(__Start>=[Start]&&__End<=[End],1,0))
        )
RETURN
    IF(MAXX(__Table,[__Contains])=1,0,__Duration)

[Julian1]

Thanks, Greg. One of the two examples I mentioned above now works perfectly, yay! 

This one still produces 2 hours where I had hoped for 1.5. I'm trying to tear this apart in DAX Studio to understand it better but would welcome thoughts if you have them. 

[Greg_Deckler]

Alright @Julian1, I've been giving this some thought. Here is my thought, the only way to be sure is to nuke this problem from orbit. Seriously though, the only way that I can think of to solve this is to forget about overlapping logic. What we need to do is find the time gaps. So here is the thought. We get the MIN of the start and the MAX of the end. This gives us the total length of time. We then subtract out the time graps where there are no meetings and voila we have the total amount of time spent in meetings.

I'm thinking that is the correct way to approach this but I'm open to other opinions.

[Greg_Deckler · Accepted solution]

@Julian1 - Alright, here is a version that should calculate the correct total number of minutes spent in meetings regardless of the overlap. Now, this version will NOT calculate the correct number of minutes at the individual row level. So, the question becomes, what do you care about, the total, the individual rows or both? If it is only really the total, this should do the trick. Updated PBIX attached. Still Table (13) and Page (13).

Duration 2 = 
    VAR __Start = MIN('Table (13)'[Start])
    VAR __End = MAX('Table (13)'[End])
RETURN
    IF(ISBLANK(MAX([Start])),BLANK(),
        VAR __Table = GENERATESERIES(__Start,__End,1/24/60)
        VAR __Table1 = ALL('Table (13)')
        VAR __Table2 = GENERATE(__Table,__Table1)
        VAR __Table3 = ADDCOLUMNS(__Table2,"Include",IF([Value]>=[Start] && [Value] <= [End],1,0))
        VAR __Table4 = GROUPBY(__Table3,[Value],"Minute",MAXX(CURRENTGROUP(),[Include]))
    RETURN
        SUMX(__Table4,[Minute])/60)

[Julian1]

Hi Greg,

I've used this wonderful solution and found that I'd like to create multiple measures based on it. Specifically, in each measure the DAX will be the same except for the GROUPBY statement used. I could copy and paste what's below into each measure and just tweak the GROUPBY statement. However, is there a more efficient way where I can have all the lines above the GROUPBY execute once and have each measure use that resultant value with a different GROUPBY? 

This would also make it simpler to maintain, as if I want to make a change to the __Start or __End values I can do it in just one measure versus multiple.

[Greg_Deckler]

@Julian1 - If I understand correctly, no. I wish we could create measures that returned tables because that would be soooooo powerful, but alas no. Please remember to @ me I almost missed this!

[Julian1]

Thanks so much! I put this in DAX studio, stepped through it one step at a time to understand it, and it makes sense. I appreciate you helping me learn (I'm on day 4 of DAX so far). 

As one questions for you, I noticed this line creates a lot of rows:
    VAR __Table2 = GENERATE(__Table,__Table1)

If __Start and __End are far apart, __Table will have a lot of rows since there's one minute per row. __Table2 multiples that times the number of meetings I'm trying to evaluate. What would you think about instead of creating __Table2 in this way, do something like:

VAR Table__2 = ADDCOLUMNS(__Table1, "Include",<If [Value] overlaps with any of the meetings, set to 1, otherwise zero>)

I'm not sure how to express this, but perhaps an option like doing a FILTER on __Table and having the condition you're checking for be if the  [Value]>=[Start] && [Value] <= [End]. If 1 or more rows are returned, you know one meeting overlaps [Value] so you set the "Include" column to 1. This would generate a lot of FILTER calls, but could perhaps avoid generating a large table. 

Would that work and/or make sense? I'm mostly trying to learn so appreciate the insight. 

[Greg_Deckler]

@Julian1 - I'd have to think through that, the purpose of that GENERATE statement is to get a list of minutes between the start and end dates and then you basically check each one to see if it is in a meeting or not.

[Julian1]

Hey Greg,

I wanted to circle back and let you know I've made good use of your assistance here and learned a lot in the process. Thanks verry much, I will mark as an answer.

[Greg_Deckler]

@Julian1 - Oh boy, that's an interesting one, I'll have to give that some thought! In terms of the other question about coding and debugging, it's why I like to code with variables step-by-step versus some huge single, nested CALCULATE statement. Sooooo much easier to just take your return code put // in front of it and just return a variable. In this way you can basically step through your DAX code (like a break point) and observe values at each step in the calculation. It's old school debugging but still works famously! I also find that coding DAX this way makes the code far more readable and easier to understand and read.

Anyway, let me think about this one.

[Julian1]

Wonderful, thanks!

Do you happen to have a preferred method to step through a query like this, like source code in a debugger? I've started experimenting with DAX studio so I can learn what it's doing and not just copy and paste to educate myself. I welcome any tips you have on that so I can grow my knowledge here. 

Thanks!

[Greg_Deckler]

@Julian1 - No, it's not wrong data, it's a boundary case that I had thought of but didn't have enough cycles to address at the time/was being lazy. Let me put some brain cycles to it and see what I can do.

[samdthompson]

you need to sum for when the start time is blank eg the total. Try:

=IF(
     HASONEVALUE(CollaborationStartTime),
          [DurationHours],
          MAX(CollaborationEndTime)-MIN(CollaborationStartTime)
    )