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
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
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?
YJResolver II
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
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
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
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.