Resource Utilization per Week

[Anonymous]

Hello all, 

I am trying to calculate the utilization rate of resources. The utilization is calculated by billable hours / capacity. 

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. 

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. 

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 solution]

Hi,

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.

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]
            && 'Work'[Date] >= Capacity[startOfWeek]
            && 'Work'[Date] <= Capacity[startOfWeek] + 6
    )
)

Example report

[Anonymous]

Thank you very much for your answer!

I ended up modifying the solution a little bit to fit my needs, but I used your dax calculation as a base for my solution.