Forum Discussion
Spilt Calculated Duration with Overlapping Dates for Same Record - Power Query
Hello,
I've spent quite some time trying to resolve an issue related to calculating "Out of Compliance Days" for a dataset, and I could really use some help. I've searched through the forum, but so far, I haven't found a solution that fits my specific scenario.
I'm working with a dataset similar to the tables below: students are required to submit their projects within 10 days. However, complications arise when students go on sick leaves, leading to the creation of duplicate records for the same student and project. When a student is on sick leave, the system generates a resolution date, set to 5 days after the last sick day.
Please note that whether a student is on sick leave or not, "Out of Compliance Days" should still be calculated based on the days between the project due date and the submission date.
The standard calculation of the duration between "Submission Date" and "Project Due Date," falls short when there are multiple exceptions, as shown in the example for Project ID 532:
| Student Name | Project ID | Project Due Date | Sick Start Date | Sick End Date | Resolution date | Submission Date | Out Of Compliance Days |
| Student 17 | 532 | 6/11/2023 | 6/2/2023 | 6/8/2023 | 6/13/2023 | 6/16/2023 | 5 |
| Student 17 | 532 | 6/11/2023 | 6/9/2023 | 6/15/2023 | 6/20/2023 | 6/16/2023 | 5 |
| Student 10 | 489 | 4/2/2023 | 4/26/2023 | 4/27/2023 | 5/2/2023 | 4/28/2023 | 26 |
| Student 03 | 601 | 4/27/2023 | 4/28/2023 | 4/28/2023 | 5/3/2023 | 4/29/2023 | 2 |
| Student 08 | 398 | 3/30/2023 | 4/22/2023 | 4/24/2023 | 4/29/2023 | 4/24/2023 | 25 |
I'm looking to add a column that calculates and splits the "Out of Compliance Days" by exception for the same project ID. I expect the results to reflect this:
| Student Name | Project ID | Project Due Date | Sick Start Date | Sick End Date | Resolution date | Submission Date | Out Of Compliance Days |
| Student 17 | 532 | 6/11/2023 | 6/2/2023 | 6/8/2023 | 6/13/2023 | 6/16/2023 | 2 |
| Student 17 | 532 | 6/11/2023 | 6/9/2023 | 6/15/2023 | 6/20/2023 | 6/16/2023 | 3 |
| Student 10 | 489 | 4/2/2023 | 4/26/2023 | 4/27/2023 | 5/2/2023 | 4/28/2023 | 26 |
| Student 03 | 601 | 4/27/2023 | 4/28/2023 | 4/28/2023 | 5/3/2023 | 4/29/2023 | 2 |
| Student 08 | 398 | 3/30/2023 | 4/22/2023 | 4/24/2023 | 4/29/2023 | 4/24/2023 | 25 |
I'm at a loss as to how to achieve this, and I would greatly appreciate any help, tips, or guidance. Thanks in advance.
Hi AquaLyzer ,
Here's the result:
You can check mroe details from my attachment.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi AquaLyzer ,
Here's the result:
You can check mroe details from my attachment.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.