cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Weekly & Yearly Resource Utilization Tracker

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.

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

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

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.

2 REPLIES 2
Community Support

Hi @Anonymous ,

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

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
Not applicable

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors