Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
bigrods
Helper II
Helper II

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 NoFirst NameLast NameAbsence Start DateAbsence End DateDays LostSiteManager
123456BobJones07-Aug-24 49NewtownJim
987654MartyWilliams24-Aug-2425-Aug-242OldtownCarl
123456BobJones07-Aug-24 24NewtownHelen

 

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 NoFirst NameLast NameAbsence Start DateAbsence End DateNo. Days Lost
123456BobJones07-Aug-24 73
987654MartyWilliams24-Aug-2425-Aug-242

 

- but I'm not sure how to get this - if anyone could please have a look and any suggestions would be extremely grateful!

1 ACCEPTED SOLUTION
v-junyant-msft
Community Support
Community Support

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:

vjunyantmsft_0-1736990213366.png


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.

View solution in original post

2 REPLIES 2
v-junyant-msft
Community Support
Community Support

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:

vjunyantmsft_0-1736990213366.png


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!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.