Forum Discussion
Calculating Vacation Day Ratio
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:
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
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?
Resolver IIThanks 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
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
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:
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