Forum Discussion
Time between two dates columns whilst not double counting same date & time periods
| 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.
4 Replies
- Greg_Deckler
Community Champion
AverageBiUser This sounds like Overlap: Overlap - Microsoft Power BI Community
3 1 2A 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- Greg_Deckler
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.
