Weekly & Yearly Resource Utilization Tracker

Question

Hello,&nbsp;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.&nbsp;&nbsp;Raw Data:&nbsp;&nbsp;NameDesignation&nbsp;Week EndingTotal Hours workedPerson 1Manager04-Jun-2120Person 1Manager11-Jun-2130Person 1Manager18-Jun-2125Person 2&nbsp;Senior Manager04-Jun-2140Person 2&nbsp;Senior Manager11-Jun-2140&nbsp;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.&nbsp;&nbsp;The weekly utilization is calculated simply as: "Total Hours worked"/45&nbsp;The total yearly utilization is calculated based on&nbsp; 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%&nbsp;&nbsp;Desired output is in the form of a PowerBI Matrix Visual , and should look something like this:&nbsp;&nbsp;Team MemberDesignation&nbsp;04-Jun-2111-Jun-2118-Jun-21&nbsp;Total Utilization&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Person 1Manager44%66.67%56%&nbsp;56%Person 2Senior Manager89%89%80%&nbsp;86%&nbsp;&nbsp;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.&nbsp;&nbsp;I also tried this&nbsp;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.&nbsp;&nbsp;P.S: I am using a 1st July to 31st June calendar.&nbsp;&nbsp;Kindly help me.&nbsp;&nbsp;Thanks.&nbsp;&nbsp;

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
Based on my test, there are 53 (52weeks + 1 day) weeks in 2021.
&nbsp;
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)))
&nbsp;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 QinIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Weekly & Yearly Resource Utilization Tracker

2 Replies

Related content

Utilization Rate

Resource Utilization per Week

Resource utilization per day

Less resource consumption

Staff Log Tracker

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

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%