Forum Discussion
Sickness data - duplicate rows generated with same Absence Start Date
Hi,
I wasn't really able to describe my issue in the Title bar, hope I can here! I have some Employee Sickness data that I need to identify the Sickness Ratio per month from, but the data is displayed as following:
There is 1 row per sickness absence, with a Start Date, End Date and No. Days Lost - but if the employee has any changes to their employment while on sick, there is a duplicate row entered with the same Absence Start Date but the remaining Days Lost. I need to be able to identify either the whole time absent for each instance per employee e.g.
My data is as following
| Assignment No | First Name | Last Name | Absence Start Date | Absence End Date | Days Lost | Site | Manager |
| 123456 | Bob | Jones | 07-Aug-24 | 49 | Newtown | Jim | |
| 987654 | Marty | Williams | 24-Aug-24 | 25-Aug-24 | 2 | Oldtown | Carl |
| 123456 | Bob | Jones | 07-Aug-24 | 24 | Newtown | Helen |
The issue is that, for Bob Jones it looks as though there are 2 absences in August-24 when actually there has been 1. As Bob has been assigned a new manager in September, an extra row has been created (with Helen as manager) but the Absence Start Date is still showing 7th August.
Ideally I'd need to be able to have 1 row with 73 Days Lost
| Assignment No | First Name | Last Name | Absence Start Date | Absence End Date | No. Days Lost |
| 123456 | Bob | Jones | 07-Aug-24 | 73 | |
| 987654 | Marty | Williams | 24-Aug-24 | 25-Aug-24 | 2 |
- but I'm not sure how to get this - if anyone could please have a look and any suggestions would be extremely grateful!
Hi bigrods ,
You can use this DAX to create a calculated table:SummarizedAbsences = SUMMARIZE( EmployeeAbsences, EmployeeAbsences[Assignment No], EmployeeAbsences[First Name], EmployeeAbsences[Last Name], EmployeeAbsences[Absence Start Date], EmployeeAbsences[Absence End Date], "Total Days Lost", SUM(EmployeeAbsences[Days Lost]) )And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi bigrods ,
You can use this DAX to create a calculated table:SummarizedAbsences = SUMMARIZE( EmployeeAbsences, EmployeeAbsences[Assignment No], EmployeeAbsences[First Name], EmployeeAbsences[Last Name], EmployeeAbsences[Absence Start Date], EmployeeAbsences[Absence End Date], "Total Days Lost", SUM(EmployeeAbsences[Days Lost]) )And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Great, thanks a lot, this has worked!
