Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 for...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello Anonymous 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?