Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating Vacation Day Ratio

EDIT: I've explained this a bit more thoroughly in a comment below.

I've been struggling with this on my own for a bit, if someone could find a solution you would make my year!

I have two sets of data:

  • A table of all employees with their department, hire date and termination date
  • A table with rows for each vacation date and department

I would like to create a "ratio" measure that would calculate:

# OF VACATION DAYS / # OF EMPLOYEES

However, I run into a bit of a snag because I would like to be able to use "Department" as a series, and I would also like it to work across a date axis.

i.e. I'd like to be able to show a trend like, "Human Resources employees are taking less vacation this quarter compared to last."

The ratio is necessary because some departments have hundreds of people while others only have four, and I'd like to be comparing apples to apples. 

Am I making sense? :( 

Need Help
  • YJ's avatar
    YJ
    6 years ago

    There you go:

    I created a new measure

    Vacation Days_Used = COUNT(Vacation_Days_Tb[Vacation Day])
    So from previously instead of getting from the column value i get the new ratio from this measure /thats the wonders of measures, it lets you built up your solution block by block

    :

    PBIX link 

    For the Headcount i plug in the values as previosly, to calculate it from your employee table you just have to follow the solution at :

    https://community.powerbi.com/t5/Desktop/Number-of-employee-between-two-dates/m-p/398921#M182124

     

    For understanding, i proposed you attempt your solution one table/one measure at a time and if you hit specific question we are here

     

    Regards

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I can break this down a bit more maybe to make it understandable.

      This is what I have:

      Employee Table

      Employee #DepartmentHire DateTermination Date
      1HR2018-05-07 
      2HR2018-09-01 
      3Sales2019-08-01 
      4Sales2000-01-012019-01-01
      5Sales2005-01-01 
      6Finance2006-08-04 
      7Finance2004-05-08 
      8Admin2010-06-082013-05-01
      9Admin2010-06-09 
      10Admin2010-06-10 

      Vacation Days Taken Table

      Vacation DayDepartment
      2019-01-03HR
      2019-04-01HR
      2019-07-01Sales
      2019-08-04Admin
      2019-10-01Finance

      This is what I'd like to do:

      Vacation and Headcount Table

      MonthDepartmentHeadcountVacation Days Used
      2019-01-01HR21
      2019-01-01Sales30
      2019-01-01Admin30
      2019-01-01Finance20
      2019-02-01HR20
      2019-02-01Sales30
      2019-02-01Admin30
      2019-02-01Finance20
      2019-03-01HR20
      2019-03-01Sales31
      2019-03-01Admin30
      2019-03-01Finance20

      This would enable me to create a vacation ratio metic. E.g. In January of 2019, HR had a 2% absence rate.

      [ Number of vacation days ] / ( [headcount] * [number of workdays] )

      Does that clarify things?

      • YJ's avatar
        YJ
        Icon for Resolver II rankResolver II
        6 years ago

        Thanks for the clarification,

        i draft this and hope it can get you started:

        The key is that one should have a calendar table unless you can be sure that the vacation and headcount table also have the 1st date for each department. ps: in your example the vacation are mainly 0 so i took the liberity to add in more vacations=)😊

        see attached: https://drive.google.com/open?id=1wDDYLe26KWE1sGsJKXAecCnLAkMoj_L_ 

        In this example. i keep workdays as a constant of 22 each month, if need be a measure can be add to count the number of weekdays of each month, and a next level would be to include a holiday column in the calendar to substract for public holidays in your region. 

         

        The rest that happens before i guess, you have pretty much figured and it just been routine.

        Something missing out(or just cleaner) could be Employee_vacation_day_table where you have:

        [EmployeeID], [Vacation Day].

        Your source should only be (1)Employee_Table and (2)Employee_vacation_day_table

        Vacation Days Taken Table and Vacation and Headcount Table is not necessary(bothcalculated form (1) and (2)), it can be a working table if you like or temp table.

         

        Great project,

        regards