Staff Utilisation based as % of available hours minus annual leave
Hi PWR BI Family,
Your support would be most appreciated on a staff utilisation problem I have. I have read other utilisation post; however, the solutions either don't fit my use case or are a bit too complicated for me to reverse engineer.
I would like to workout each staff members utilisation rate (i.e. hours related to billable activities = Utilised Hours). I'm open to both re-modelling and DAX related solutions.
This might be 1 of a few requests all related to utilisation. I have tried a few DAX calculations; however, I'm not confident they are right and I would appreciate some fresh thinking. I will post links to all the answers and measures in one unifying post at the end.
Quick Background: ===============
Timesheet based data for a Utilisation dashboard. The data model is a simple "starish" schema.
my Utilisation data model
Business Logic: =============
The below notes aren't DAX formulas, instead just an attempet to convey the logic I wish to implement as DAX.
Utilised Hours = [Hours] with an activity type marked "Yes" within the related table 'DimActivities' [UtilisedActivityYN] column.
Working Days = 'DimCalendar'[IsWeekdayYN] = "Yes"
One Work Hr = 0.13333
Note: (0.13333 is the decimal equivalent of 1 working hour based upon 1 working day = 7.5 hrs i.e. 1/7.5 = 0.13333)
Annual Leave Taken = Sum('FactTimesheet'[Hours] were activity = "Annual leave")* 'One Work Hr'
Available Days = Count('Working Days' within month) - 'Annual Leave Taken'