Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

Calculating capacity of employee vs actual hours booked



I cannot seem to find a solution in previous posts:

My aim to to create a visual to show the capacity (in hours) a consultant can work vs the actual time he worked. To calculate the capacity per month, I have a staff data table which has each person's capacity per day, I then need to multiply this with the number of working days per given month, which I now have in my date table. Then I need to subtract any absences he/she moight have had, which I have in another table, as their absences affects their actual capacity. I have no idea how to go about this, as the staff table has only the emplyoee record once and not per month. Do I need to create a table to show per month and join it to my absence table?

In addition to this, my actual data is in another table with lots of complexities, but this data is per day and not per month. In the end I would like to display capacity (capacity hours/day * working days/month) - (Total absence hours per month) vs Actual time booked per month.

I hope someone can help me.

Frequent Visitor

Column1FullNameRegionCapacity hours/dayContractualHoursPerDayInternal RateArchived
StaffMemberHoursActual HoursCapacity hours/dayContractualHoursPerDayCapacity per month (CC)Total HoursAbsences.Total Absence HoursPeriod_Year
UserPeriod No.Total Absence HoursPeriod_Year

 I only want one value per staffmember per month for both capacity hourse per month and contractual hours per month.

instead of SUM() use MAX() for these values.


What is the difference between Hours, Actual Hours and Total Hours?


Here's how I would structure the data model  


which then would make the computations trivial.


What is the expected outcome based on your sample data?



Thank you for your reply. No, I don't beleive the calculations will not be necessary. I am trying to achieve a capacity/month number, which I can show in a column graph against Total Hours (which is the same as hours btw.). But to achieve the capacity per month number, I have to multiply with only working days and then subtract absences for that month. So:

User 1: Total Hours = 50

            Capacity = (6.24 (capacity hours per day) * 21 (working days per month)) - 8 (absence hours for month) = 123.04 for said month.

My challenge is, how do I calculate this per month when each month has a unique networkingdays and absence amount. If I use AVERAGE or MAX in the way I did it now (Capacity hours/month being repeated in every row of transactional data), I cannnot show the sum of all employees's capacities/month vs total hours. Am I making sense?

What is the expected outcome based on your sample data?

Frequent Visitor



Please find the data attached. I have 3 different tables (of which the both is joined to the transactional data, because this is the only way I knew how, but I want to avoid it). I am calculating the capacity hours per month as follows in a calculated column:


Capacity per month = NETWORKDAYS(STARTOFMONTH(EarnedSpent[Date.1]),EOMONTH(EarnedSpent[Date.1],0),1)*(EarnedSpent[Capacity hours/day])-(EarnedSpent[Absences.Total Absence Hours])
This renders a correct number per person when I use the average of this column, but if I want to compare total actuals to total capacity hours, then I have a problem because the value is repeated many time because of the join which renders an incorrect sum. I only want one value per staffmember per month for both capacity hourse per month and contractual hours per month.
Super User
Super User



Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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