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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
hsalim
Regular Visitor

Establishing monthly utilisation by employee, project, sector etc

First of all, a big thank you in dedicating your time to read my post. I’m truly grateful for your help.

 

I want to create monthly utilisation so it’s possible to see in % terms the total number of hours an employee in an organisation would be working on billable and non-billable projects.

8 hours is the maximum number of working hours per day. And employees are required to enter 40 hours of their time+activity per week.

For example if there are 20 billable days in February and the maximum number of hours is 8 per day. The total is 20 X 8 = 160 hours per month.

Therefore an employee called: ‘Joe’ may have worked 100 hours on a client project which would give us a utilisation of 100/160 – 62.5%

Please see an extract of data below:

ProjectPhaseDateUserService typeTextEffortRateFee
I10ZUK_BD_ADCOSALES06/01/2020adco100_ADMIN 8.000.000.00
I10ZUK_BD_ADCOSALES07/01/2020adco100_ADMIN 8.000.000.00
I10ZUK_BD_ADCOSALES08/01/2020adco100_ADMIN 8.000.000.00
I10ZUK_BD_ADCOSALES09/01/2020adco100_ADMIN 8.000.000.00
I10ZUK_BD_ADCOSALES10/01/2020adco100_ADMIN 8.000.000.00
I10ZUK_BD_ADCOSALES13/01/2020adco100_ADMIN 8.000.000.00
I10ZUK_BD_ADCOSALES14/01/2020adco100_ADMIN 8.000.000.00
I10ZUK_BD_ADCOSALES15/01/2020adco100_ADMIN 8.000.000.00
I10ZUK_BD_ADCOSALES16/01/2020adco100_ADMIN 12.000.000.00
I10ZUK_BD_ADCOSALES17/01/2020adco100_ADMIN 8.000.000.00
I10ZUK_BD_ADCOSALES20/01/2020adco100_ADMIN 8.000.000.00
I10ZUK_BD_ADCOSALES21/01/2020adco100_ADMIN 8.000.000.00
I10ZUK_BD_ADCOSALES22/01/2020adco100_ADMIN 11.000.000.00
I10ZUK_BD_ADCOSALES23/01/2020adco100_ADMIN 8.000.000.00
I10ZUK_BD_ADCOSALES24/01/2020adco100_ADMIN 8.000.000.00
I10ZUK_BD_ADCOSALES27/01/2020adco100_ADMIN 8.000.000.00
I10ZUK_BD_ADCOSALES28/01/2020adco100_ADMIN 8.000.000.00
I10ZUK_BD_ADCOSALES29/01/2020adco100_ADMIN 11.000.000.00

 

I have a column that already tells me the number of hours he would have worked on a billable or non-billable projects. I don’t have a column where I would be able to divide the hours on billable project over total hours per day (8 hours being maximum)

If I had that extra column, I would be able to work it out but as I’m dealing with a lot of rows, Ideally, I don’t want to be working in Excel and then moving it over to Power BI.

In addition, the total number of hours per day is fixed. 8 hours is the maximum and 160 hours is the maximum hours that will be charged to the client.

I hope that all makes sense, can anyone give me some suggestions / solutions? Thank you

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

"isn't readily available as a table "  -  then this would be your very first priority. Create a calendar table (outside of Power BI) that has that information. It's not a shame to maintain that table manually.

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

"isn't readily available as a table "  -  then this would be your very first priority. Create a calendar table (outside of Power BI) that has that information. It's not a shame to maintain that table manually.

Okay, i'll take your word for it, many thanks🙂

lbendlin
Super User
Super User

how do you know how many billable hours / working days are in a month?  Is that information in your calendar table?

What happens when they spend effort across month boundaries? Do you still cut it off at the month?

What happens when they enter nonsensical information like "25 hrs of effort" ?

To answer your questions:

1.  The total number of billable hours in a day or month isn't readily available as a table which is the challenage that I have right now. I know it's 8 because it's the 'norm'. (Sorry, that, doesn' help, does it?)

2. If an employee has 160 hours in a month, then we're interested to see in what projects those 160 hours have been allocated. What ratio of billable or unbillable would the Employee done? Also, we want to cut it off every month for reporting progress reasons

3. all the data is reviewed by a member of staff so any non-sensical information is dismissed at the early stages.

 

Does that help?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors