Calculating Vacation Day Ratio

[Anonymous]

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? 😞 

[v-shex-msft]

Hi @Anonymous ,

Please share more detail informations, it will help us to clarify your requirement and do test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Anonymous]

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

This is what I have:

Employee Table

Employee #	Department	Hire Date	Termination Date
1	HR	2018-05-07
2	HR	2018-09-01
3	Sales	2019-08-01
4	Sales	2000-01-01	2019-01-01
5	Sales	2005-01-01
6	Finance	2006-08-04
7	Finance	2004-05-08
8	Admin	2010-06-08	2013-05-01
9	Admin	2010-06-09
10	Admin	2010-06-10

Vacation Days Taken Table

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

This is what I'd like to do:

Vacation and Headcount Table

Month	Department	Headcount	Vacation Days Used
2019-01-01	HR	2	1
2019-01-01	Sales	3	0
2019-01-01	Admin	3	0
2019-01-01	Finance	2	0
2019-02-01	HR	2	0
2019-02-01	Sales	3	0
2019-02-01	Admin	3	0
2019-02-01	Finance	2	0
2019-03-01	HR	2	0
2019-03-01	Sales	3	1
2019-03-01	Admin	3	0
2019-03-01	Finance	2	0

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]

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

[Anonymous]

Thank you for putting this together! I really appreciate you taking the time to help me figure it out.

Unfortunately, my challenge is that the table Vac_Head_Tb doesn't actually exist, I created that as a sample of what I want.

All I have right now is the first two tables, the third one I showed is my desired outcome. However, I can't figure out how to transform the first to tables to result in Vac_Head_Tb. 

[YJ · Accepted solution]

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

[YJ]

Hi,

can i ask what is the link between the Sick day under vacationdays taken table and Vacation days used under vacation and headcount table?

regards

[Anonymous]

Sorry for the confusion, it's supposed to be vacation. I'm starting with vacation and would like to move to sick days after to create an all absences rate.