Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to Solution.
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.
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
145 | |
73 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |