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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
BellaRauch
Frequent Visitor

Calculating capacity of employee vs actual hours booked

Hello,

 

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.

6 REPLIES 6
BellaRauch
Frequent Visitor

Column1FullNameRegionCapacity hours/dayContractualHoursPerDayInternal RateArchived
1User1NL6.247.8€95.32FALSE
1User2US4.86€110.8FALSE
1User3China6.48$125.03FALSE
StaffMemberHoursActual HoursCapacity hours/dayContractualHoursPerDayCapacity per month (CC)Total HoursAbsences.Total Absence HoursPeriod_Year
User13.53.56.247.8129.2878112023
User10.50.56.247.8135.520.5882023
User10.50.56.247.8143.520.5032023
User2666.247.8116.86342023
User213.56.247.8129.2872112023
User250.56.247.8135.520.5582023
User340.56.247.8143.520.5232023
         
         
UserPeriod No.Total Absence HoursPeriod_Year
User1118112023
User14842023
User153252023
User28882023
User273272023
User251652023
User323622023
User336032023
User3411242023

 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  

lbendlin_0-1699579634765.png

which then would make the computations trivial.

 

What is the expected outcome based on your sample data?

Hi,

 

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?

BellaRauch
Frequent Visitor

Hi,

 

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.
lbendlin
Super User
Super User

read about GENERATESERIES.

 

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 https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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