Weekly & Yearly Resource Utilization Tracker

[Anonymous]

Hello,

I am trying to create a resource utilization tracker for employees which tracks utilization as a % of total working hours (45 per week). The output is expected in a weekly and a yearly format (details below) however I am facing an issue in the Yearly utilization values. 

Raw Data: 

Name	Designation	Week Ending	Total Hours worked
Person 1	Manager	04-Jun-21	20
Person 1	Manager	11-Jun-21	30
Person 1	Manager	18-Jun-21	25
Person 2	Senior Manager	04-Jun-21	40
Person 2	Senior Manager	11-Jun-21	40

This raw data is updated every week with the new week's data being added to it, and is only available for the no. of weeks that a person has been employed in the organization, which means if someone joined 2 weeks ago, data for only 2 weeks would be available for that employee. 

The weekly utilization is calculated simply as: "Total Hours worked"/45 

The total yearly utilization is calculated based on  SUM(Total Hours worked)/(45*No of weeks completed in the year)

Which means if someone joined in the last week of a financial year and worked for 40 hours, then her yearly utilization would be 40/(45*52) = 1.7% 

Desired output is in the form of a PowerBI Matrix Visual , and should look something like this: 

Team Member	Designation	04-Jun-21	11-Jun-21	18-Jun-21		Total Utilization
Person 1	Manager	44%	66.67%	56%		56%
Person 2	Senior Manager	89%	89%	80%		86%

Simple measures like DIVIDE(SUM('Utilization'[Total Hours worked]),45) are not working for me, as the 'Total' column is not reflecting the correct values. 

I also tried this solution which gives me the correct weekly utilization but does not give a correct yearly utilization, as the SUMMARIZE function is reflecting the no. of weeks for which data is available for an employee/team member but I need the denominator to reflect the number of weeks that have passed. 

P.S: I am using a 1st July to 31st June calendar. 

Kindly help me. 

Thanks.  

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

Based on my test, there are 53 (52weeks + 1 day) weeks in 2021.

Please follow these steps:

1. Create a Calendar table:

Calendar = DISTINCT( SELECTCOLUMNS( CALENDAR(DATE(2019,1,1),DATE(2022,12,31)),"Year",YEAR([Date]),"Week",WEEKNUM([Date],2)))

 2. Add Week column to original table:

Week = CALCULATE(MAX('Calendar'[Week]),FILTER('Calendar','Calendar'[Year]=EARLIER('Table'[Week Ending].[Year])))

3. Then using the following formula to create measure:

percentage = IF(ISINSCOPE('Table'[Week Ending]), DIVIDE(SUM('Table'[Total Hours worked]),45), DIVIDE( 45*MAX('Table'[Week]),SUM('Table'[Week Ending]) ))

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @v-eqin-msft thanks for your efforts, I was able to make this work with some modifications as per my data set. 

Now I would like to conditionally format these output values based on a column (This is basically the utilization target for each designation eg. senior manager, manager, etc.) that I have in my data set.  

If in any given week the utilization target of a Person (at a given designation) has been achieved, the cell colour should be green else yellow. 

Could you or anyone else in the community please guide me on how to do this, as I am relatively new to Power BI and could not get conditional formatting to work on my own after exploring several different resources?