Forum Discussion

setis's avatar
setis
Post Partisan
6 years ago
Solved

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!

Need Help
  • az38's avatar
    az38
    6 years ago

    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

     

7 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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

     

    • setis's avatar
      setis
      Post Partisan
      6 years ago

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

  • Tahreem24's avatar
    Tahreem24
    Super User
    6 years ago

    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.
     

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

     

     

     
    • setis's avatar
      setis
      Post Partisan
      6 years ago

      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"

      • Tahreem24's avatar
        Tahreem24
        Super User
        6 years ago

        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!