topic Re: Time between two dates columns whilst not double counting same date & time periods in DAX Commands and Tips

Time between two dates columns whilst not double counting same date & time periods

AverageBiUser — Tue, 10 Jan 2023 14:18:06 GMT

1	2
06/01/2020 08:00:00	06/01/2020 19:30:00
06/01/2020 10:35:00	06/01/2020 11:40:00
06/01/2020 08:00:00	06/01/2020 19:30:00
06/01/2020 10:35:00	06/01/2020 11:40:00
11/02/2021 08:00:00	11/02/2021 17:00:00
11/02/2021 08:00:00	11/02/2021 17:00:00
11/02/2021 08:00:00	11/02/2021 17:00:00
11/02/2021 08:00:00	11/02/2021 17:00:00
12/02/2021 08:00:00	12/02/2021 14:25:00
12/02/2021 08:00:00	12/02/2021 18:00:00
12/02/2021 15:00:00	12/02/2021 15:20:00
12/02/2021 08:00:00	12/02/2021 14:25:00
12/02/2021 08:00:00	12/02/2021 18:00:00
12/02/2021 15:00:00	12/02/2021 15:20:00
13/02/2021 07:00:00	13/02/2021 20:10:00
13/02/2021 08:40:00	13/02/2021 09:00:00
13/02/2021 17:00:00	13/02/2021 18:00:00
13/02/2021 07:00:00	13/02/2021 20:10:00
13/02/2021 08:40:00	13/02/2021 09:00:00
13/02/2021 17:00:00	13/02/2021 18:00:00

I am trying to figure out how to calculate the difference between 1 & 2 (datediff) where the same date & time period are not double counted. E.G. With a calculated column & regular date diff function; Record 1 would show 11.5 hours & record 2 would show 1.083 hours (65 minutes).

However, the 65 minutes is being double counted as this same time period (between 10:35:00 - 11:40:00) is already counted in the first record, as this record calculates time in between 8:00:00 & 19:30:00 on the same day.

I need the total time between the two date columns that is not double counted or "nested" within another record.

Thanks in advance.

Re: Time between two dates columns whilst not double counting same date & time periods

Greg_Deckler — Tue, 10 Jan 2023 14:19:46 GMT

AverageBiUser This sounds like Overlap: Overlap - Microsoft Power BI Community

Re: Time between two dates columns whilst not double counting same date & time periods

AverageBiUser — Tue, 10 Jan 2023 16:19:14 GMT

3 1 2

A	06/01/2020 08:00:00	06/01/2020 19:30:00
A	06/01/2020 10:35:00	06/01/2020 11:40:00
A	06/01/2020 08:00:00	06/01/2020 19:30:00
A	06/01/2020 10:35:00	06/01/2020 11:40:00
B	11/02/2021 08:00:00	11/02/2021 17:00:00
B	11/02/2021 08:00:00	11/02/2021 17:00:00
B	11/02/2021 08:00:00	11/02/2021 17:00:00
B	11/02/2021 08:00:00	11/02/2021 17:00:00
B	12/02/2021 08:00:00	12/02/2021 14:25:00
B	12/02/2021 08:00:00	12/02/2021 18:00:00
B	12/02/2021 15:00:00	12/02/2021 15:20:00
B	12/02/2021 08:00:00	12/02/2021 14:25:00
B	12/02/2021 08:00:00	12/02/2021 18:00:00
B	12/02/2021 15:00:00	12/02/2021 15:20:00
B	13/02/2021 07:00:00	13/02/2021 20:10:00
B	13/02/2021 08:40:00	13/02/2021 09:00:00
B	13/02/2021 17:00:00	13/02/2021 18:00:00
B	13/02/2021 07:00:00	13/02/2021 20:10:00
B	13/02/2021 08:40:00	13/02/2021 09:00:00
B	13/02/2021 17:00:00	13/02/2021 18:00:00

Hi Greg, thanks for you reply. Unfortunately, the measure provided is not working with Power Bi as it can't load any values and it stuck "working on it" for long periods of time.

Also, I forgot to add that the time difference calculation (with no overlap) needs to be done for each distinct value for column 3 (as above)

Greg_Deckler

Re: Time between two dates columns whilst not double counting same date & time periods

Greg_Deckler — Tue, 10 Jan 2023 23:10:16 GMT

AverageBiUser Well, it is an EXTREMELY expensive operation to do it the way that I was forced to do it. No argument there. But that was the only way I could get the correct results when I put that measure together. It's not a trivial problem.

Re: Time between two dates columns whilst not double counting same date & time periods

AverageBiUser — Wed, 11 Jan 2023 11:20:53 GMT

Hi Greg, after implementing the column, the values for each record are returning 1 distinct value that is wrong. Do you know how I would rectify the issue?