Forum Discussion

setis's avatar
setis
Icon for Post Partisan rankPost 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) lik...
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

     

Tahreem24's avatar
Tahreem24
Icon for Super User rankSuper 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
Icon for Post Partisan rankPost 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
    Icon for Super User rankSuper 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!

     

     

    • setis's avatar
      setis
      Icon for Post Partisan rankPost Partisan
      6 years ago

      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

  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    6 years ago

    setis ,

     

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

     

     

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