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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
setis
Post Partisan
Post Partisan

Calculating sick days

Dear experts, 

 

I am trying to calculate the number of sick days per month. 

 

The challenge is that there are instances where an employee has 2 lines per day (due to the shift distribution) like this:

 

WorkDaySalary IDTimesheetWorkTimeStartWorkTimeEnd
31-12-20191234Illness31-12-2019 06:3031-12-2019 09:00
31-12-20191234Illness31-12-2019 13:0031-12-2019 15:30
30-12-20191234Normal30-12-2019 06:3030-12-2019 15:30
30-12-20191935Normal30-12-2019 06:3030-12-2019 09:00

 

 

I would like to count the Sick days, not the sick shifts, it that makes sense.. The desired result for this would be 1. 

 

I've tried using DISTINCTCOUNT but this gives me the number of different employees that has been sick during the chosen period. 

 

How should I proceed?

 

Thanks in advance!

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

hi @setis 

try a calculated table

Table = summarize(FILTER('Table';'Table'[Timesheet]="illness");'Table'[Salary ID];"illnessdays";distinctcount('Table'[WorkDay]))

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

7 REPLIES 7
Tahreem24
Super User
Super User

@setis ,

 

You can simply create a Measure like Below:

SickDay = CALCULATE(DISTINCTCOUNT(File[Timesheet]),File[Timesheet]="Illness")
 
It will give you 1 as result. For your reference I have replicated your data and problem at my side and got the 1 result for SickDay.
Capture.JPG
 

Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

 

 

 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Tahreem24 Thanks for your answer. 

 

I don't think that this is what I'm looking for.

 

If you add a new line with a different SalaryID and Timesheet = "Illness" the result would still be 1 and my desired result would be 2. Because there are 2 workdays labeled as "Illness"

@setis ,

 

Instead of DISTINCTCOUNT use COUNT function. Refer below screen shot:

Capture.JPG

 

 

Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@setis ,

 

Use the below measure instead:

SickDay = CALCULATE(COUNT(File[Timesheet]),File[Timesheet]="Illness")
 

Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Tahreem24 , thanks but the problem with this is that the first to lines in my example would be counted as 2 days and not one

az38
Community Champion
Community Champion

hi @setis 

try a calculated table

Table = summarize(FILTER('Table';'Table'[Timesheet]="illness");'Table'[Salary ID];"illnessdays";distinctcount('Table'[WorkDay]))

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 This works, thanks, Would it be possible to obtain the same result with a measure instead?

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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