Resource Utilization per Week

Question

Hello all,&nbsp;&nbsp;I am trying to calculate the utilization rate of resources. The utilization is calculated by billable hours / capacity.&nbsp;I have two tables, one of which consists of the resource name, person code, reported hours, date of work etc. and another table that consists of the resource name, person code, start of week, capacity. The tables are linked through the person code.&nbsp;I have attached a picture of the example tables.The problem I'm having is how to calculate the utilization rate, when the reported hours are reported per day, and the capacity is per week?I have merged these columns together, so that I have table that has both the reported hours per day, and the capacity for the whole week. For this table I would like to be able to create a calculated column that would have the sum of hours per week. Below is a picture to demonstrate. Columns "Sum of Hours per Week" and "UtilizationRate" would be calculated columns.&nbsp;I have not been able to create the column "Sum of Hours per Week". Is it possible to create a calculated column like this?Thanks for any advice!Best Regards,Henrik

sturlaws · Accepted Answer

Hi,&nbsp;are you sure you want merge the two tables? It will get you in trouble if you want sum the capasity, since the capasity is repeated for each workday.&nbsp;You can use this dax to create a calculated column summing the work hours into the capacity-table:Hours per week =
CALCULATE (
    SUM ( 'Work'[Hours] );
    FILTER (
        'Work';
        'Work'[rName] = Capacity[rName]
            &amp;&amp; 'Work'[Date] &gt;= Capacity[startOfWeek]
            &amp;&amp; 'Work'[Date] &lt;= Capacity[startOfWeek] + 6
    )
)&nbsp;Example report

Forum Discussion

Resource Utilization per Week

2 Replies

Related content

Utilization Rate

Resource utilization per day

TimeSheet Report - Resource Utilization for Week/Month

Computing Resource Utilization Per Day, Week and Month

Computing Resource Utilization Per Day, Week and Month