Count duration over overlapping dates across multiple date range

[akul]

Hi everyone!

I have been looking for a way to find the day duration for selected day after removing overlapping time.

For eg: If I select 31st July 2020 as the date, I get the table on the right. All the Ids have a Start and End DateTime. Since the data is for 31st July 2020, I only need to consider times here and these I have marked with red box in the Id-Time grid (in the pic).
But in this case, ids 1, 3 and 4 have overlapping time range. 
I would like to remove the overlaps and then calculate the duration (see total row in the image)

In this image, Ids 2 and 5 are not overlapping and contribute 5hrs and 1hr respectively to the duration count. However, ids 1, 3 and 4 overlap and their combined duration becomes 6hrs. Due to this we get a total of 12 hours. 

I need to find a way to calculate this total duration after removing overlaps.

Any possible help for this scenario would be highly appreciated. 

Thanks!

[AlB · Accepted solution]

Hi @akul 

Interesting. Try this measure. See it at work in the attached file. I have created a test table with two days. You might have to adapt it to meet your exact requirements but you'll get the idea. Do note that this code counts the full hours, i.e. the 13:00-14:00 period for instance will not be counted if there's a row starting at 13:30 and finishing at 14:30. You can also tweak that if the requirement is different.

Measure =
SUMX (
    GENERATE ( DISTINCT ( Table1[Date] ), GENERATESERIES ( 0, 23 ) ),
     (
        COUNTROWS (
            FILTER (
                CALCULATETABLE ( Table1 ),
                Table1[Start] <= ( Table1[Date] + ( [Value] * ( 1 / 24 ) ) )
                    && Table1[End] >= ( Table1[Date] + ( ( [Value] + 1 ) * ( 1 / 24 ) ) )
            )
        ) > 0
    ) * 1
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[akul]

@AlB Thanks a lot. Amazingly done. 

I had initially tried this same logic but in PowerQuery. But issue with PowerQuery was that I had to let go of dynamic filtering of this data. But this logic in DAX makes a lot of sense and works perfectly. Wish my dax was this good, I wasn't able to formulate this.

However, I have 1 question, how does the (COUNTROWS(...) > 0) * 1 part work? Countrows() would return a whole number. If you compare this whole number with zero, it would return a boolean value. And boolean value * 1 would only result 0 or 1. I think I might be missing something crucial here. Please let me know. 

Again, thanks for the elaborate solution. 🙂

[AlB]

@akul 

What was the problem exactly on PQ? Can you elaborate on that? Perhaps we can find a way

We just need to know whether there's some row "active" on that hour slot. That's what we are doing with the COUNTROWSS()>0. By multiplying by 1 we'll get a 1 when the boolean is TRUE, as you very well said. And that is what we want, right? A one in that hour slot so that when we run the SUMX, we'll have the number of hour slots "active" on that day.

Great explanation by the way.  Very clear and effective getting the message across on a non-trivial requirement. The only thing I missed was a copy of the sample data in text-tabular format on top of the pics. So that it can be readily copied and used for running a quick test.  

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers