Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Employee Absenteeism

Dear all, 

 

I am making a PBI report in order to calculate employee absenteeism percentages. 

 

There are two main tables, that are simplified and illustrated below:

Employment data:

EmployeeIDStart dateEnd dateDepartmentID
Mr. Doe12341-Jan-1631-Mar-17Sales
Mr. Doe12341-Apr-17 Sales
Ms. Jones67893-Apr-122-Apr-13Supply Chain
Ms. Jones67893-Apr-136-Jun-17Supply Chain
Mr. Smith78344-Sep-17 Finance
Ms. Williams416717-Oct-181-Jan-19HR
Mr. Moore69918-Feb-1631-Dec-17Sales

 

Indicating when a certain employee started/quit working. When a new employment contract is made, a new line is created (see Mr. doe and Ms. Jones for example). When there is no end date, the person is still working at the company. 

 

Absenteeism data:

EmployeeIDStart SickBack at workPercentage sick
Ms. Jones67896-Nov-143-Mar-16100
Ms. Jones67894-Mar-1610-Mar-1690
Ms. Jones678911-Mar-1630-Mar-1670
Ms. Jones678931-Mar-1621-Apr-1630
Mr. Doe12344-Feb-166-Feb-16100

 

Indicated when a person got sick, and when they were back at work. A person can be sick according to percentages. The number of sick days is calculated by substracting "Back at work" from "Start Sick", so in Mr. Doe's case, 2 sick days, and Ms. Jones 483 days at 100 percent, 6 days at 90 percent, etc.

 

The challenge:

The company essentiallly wants to know two factors: number of times an employee is sick (Result = 1x Ms. Jones as she was sick one time for the period from 6 November 2014 until 21 April 2016, and 1x Mr. Doe) and the percentage of sickness per department. The desired outcome is as follows: Mr. Doe is working on the Sales department. In Jaunari 2016, only Mr. Doe worked on the department, but no sick days were recoreded. Percentage sick in January is, therefore, 0. In February 2016, however, another employee was hired (Mr. Moore).  Mr. Doe was sick for 2 days. 2 sick days / (28 days in February for Mr. Doe + 20 days in February for Mr. Moore) = 4.17 percent. 

 

Weekend, official holidays don't matter for the calculation of employee absenteeism, so you can ignore them. 

 

How should I structure my data, make columns, or perform calculations, in order to come to the desired solution? 

 

I hope anybody can help me. If it is unclear, please let me know, and I can further ellaborate. 

 

Best regards,

 

Patrick

Need Help

5 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Anonymous 

    Based on my research, you could try this way as below:

    Step1:

    Add a date table, Dim Employee Id and name table, DepartmentID table

    For example:

    Date = CALENDAR(MIN('Employment data'[Start date]),TODAY()+1)
    DimEmployee = SUMMARIZE('Employment data','Employment data'[ID],'Employment data'[Employee])

    Step2:

    Add a new end date column for Employment data

    New End date = IF(ISBLANK('Employment data'[End date]),TODAY()+1,'Employment data'[End date])

    Step3:

    Create two new New Employment data table and New Absenteeism data by this formula

    New Employment data = FILTER(GENERATE('Employment data','Date'),'Date'[Date]>'Employment data'[Start date]&&'Date'[Date]<='Employment data'[New End date])
    New Absenteeism data = FILTER(GENERATE('Absenteeism data','Date'),'Date'[Date]>='Absenteeism data'[Start Sick]&&'Date'[Date]<'Absenteeism data'[Back at work])

    Step4:

    Create the relationship as below:

    Step5:

    Then create this measure for percentage of sickness per department

    percentage of sickness = DIVIDE(CALCULATE(COUNTA('New Absenteeism data'[Employee])),CALCULATE(COUNTA('New Employment data'[Employee])),0)

    By the way: there are 29 days on February 2016, so it should be

    2 sick days / (29 days in February for Mr. Doe + 21 days in February for Mr. Moore) = 4 percent

    Result:

    and here is pbix file, please try it.

     

    Best Regards,
    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-lili6-msft ,

       

      Thank you so much for your help. It seems to work! I have some more questions to make the report even better. 

      1. There is a lot of data, as the company records trace back to 1959. As a result, over 2 million rows are created in the "New Employment Data" sheet and the file becomes very heavy to load. Is there a way to reduce this? Seeing the absenteeism percentages from 1/1/2018 would be fine, as there is no reason to see the data before this date. Of course employees can be sick before 1/1/2018, and coming back to work after 1/1/2018, so you want to count the days sick in 2018 for them too.
      2. Employees that are still sick have no "Back to work" date, as that field is still empty. Currently they are not added in the overview. How can I add the ones currently sick? 
      3. Is there any way of including the percentage sick? An employee can be 50% sick for example, therefore you don't want to count 1, but 0.5 for each day the employee is sick. 
      4. Lastly, the company makes a distinction between short, middle, and long absenteeism. First 7 days are considered "short", if the employee is still sick then between 8 days and 42 days it is considered "middle", and more than 42 days absent is considered "long". Is there anyway of incorporating this into the formula?

       

      I hope you can help me with this. Thanks in advance. 

       

      Best regards,

       

      Patrick

       

       

       

       

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        7 years ago

        hi, Anonymous 

        1.for start and end date are in two columns, and for your requirement, it will create a big  "New Employment Data" table. and you may do some filter in the formula, eg. filter unnecessary Employee or date.

        2.You could add new Back at work date column like this and then use it in other dax

        New End date = IF(ISBLANK('Employment data'[End date]),TODAY()+1,'Employment data'[End date])
        3. You could add a conditional in the formula
        percentage of sickness = DIVIDE(CALCULATE(COUNTA('New Absenteeism data'[Employee]))*CALCULATE(AVERAGE('New Absenteeism data'[Percentage sick])),CALCULATE(COUNTA('New Employment data'[Employee])),0)
        4. If there are multiple rows of data for one Employee, for example: for Ms. Jones
        Employee ID Start Sick Back at work Percentage sick
        Ms. Jones 6789 6-Nov-14 3-Mar-16 100
        Ms. Jones 6789 4-Mar-16 10-Mar-16 90
        Ms. Jones 6789 11-Mar-16 30-Mar-16 70
        Ms. Jones 6789 31-Mar-16 21-Apr-16 30
        how to judge it? could you please explain it with the above sample data.
         
        Best Regards,
        Lin