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 IIHi,
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
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.