Power BI Challenge 16 - Timesheet Utilisation Reporting

Challenges Timesheet Utilisation Reporting
1

Hello Everyone,

I trust that you are all keeping well. The weeks seem to be absolutely flying!

Here we are again for another instalment of the Enterprise DNA challenge.

With every passing challenge the standards and the art of possible within power BI are stretched. We are genuinely seeing entries that are at the forefront of power bi development.

Some of the techniques on display are just incredible and there is just so much to learn from each and every entry that came in.

The write ups, the idea sharing, tips, tricks make the challenge what it is and its genuinely amazing to see the support the community lends and wants to help each other improve.

Be sure to check out the previous challenge entries here.

If you want to learn more about the challenge, then please see the link below.

As always any questions, issues, suggestions or comments please get in touch.

The Brief

How many productive hours do you work in a week? 20 maybe 30

How many should you be doing?

How many hours am I doing on a particular project?

As you will all know for many businesses and consultants these are key questions they ask when they are selling or tracking their time.

How effective is the resource? how is it performing?

Utilisation.

This weeks challenge is all about helping answer all these questions and take a deep dive into the world of utilisation and timesheet data.

The data is not overly complex and contains what would be expected columns in any time tracking solution so could easily be extended to your own work.

The data has been taken from a consultant in the field with some of the values anonymised to protect confidentiality.

Making it perfect practice for what you are likely to face in the real world.

We want to make these challenges accessible to everyone, so I am also attaching an existing PBIX with the data so that nobody is starting from zero.

The PBIX contains a comprehensive report that we should definitely review before diving into this challenge.

This challenge is all about what you can interpret from this data. What information is being overlooked. What should we be focussing on?

The aim of this challenge isn’t to be prescriptive at all. Hopefully the dataset is simple enough that it will allow us all to present our own individual analysis on the subject.

I am also attaching below a couple of links to help with research and understanding and some KPI ideas.

https://www.metric.ai/metricopedia/utilization

Submission Categories

Winning Enterprise DNA member- Only open to current Enterprise DNA members

First Time Participant winner- open to any Enterprise DNA member who is taking part in the challenge for the first time.

Winning Non-member- Open to everyone

Most Creative Entry- Open to everyone

There are some excellent prizes on offer from free membership and goodies to having your work showcased across the enterprise platform. So please do get involved and share this opportunity with others.

If you are not already following Enterprise DNA on LinkedIn please do so. There will be some great opportunities posted via LinkedIn during the challenge.

How to submit:

  • Email the complete PBIX file to powerbichallenge@enterprisedna.co
  • Take an image and URL of your report and post it to the Enterprise DNA forum
  • Take the image and URL and post it LinkedIn tagging Enterprise DNA saying I accepted the challenge.
  • We always encourage all participants to do a writeup and share their experience of participating in the challenge and sharing it on the forum and on social media.

IF YOU NEED ANY HELP WITH PUBLISHING, PLEASE REACHOUT TO ONE OF THE TEAM WE CAN HELP.

Hopefully with this challenge there is something for everyone to work on. Whether you want to focus on visulisation or the data calculation side the choice is yours.

The submission date for this challenge is Sunday, 3rd October 2021

As always, best of luck!

Any issues or questions do not hesitate to get in touch with myself or the team.

Take care and speak soon.

Haroon

Enterprise DNA

Challenge 16.zip (199.4 KB)

14 Likes
2

OK, first question.

Can we add data to the data set ? Or we can only use the data that we have ?

I would add things like employee name or number

Also, If i want to make a Utilization KPI, I need Billiable Hours / Total Hours. I have billable hours (Duration in the data set) but not Total hours (which is the expected possible hour)
Worked 4 hour of of 8 = 50% Utilization

I don’t where of if there are the rules for challenges ?

Eric (Montreal, Canada)

1 Like
3

It would be good to have an agreed set of assumptions for this challenge

Vacation days a year - 10days?
Public Holidays - 7 days?
Daily hours - 8 hours?
Nice to have would be employee salary

4

Yes, I agree KimC

My suggestion is 15 days, Vacation days a year

Public Holidays - 9 days here Montrel, Canada

  • [New Year’s Day (Friday, January 1, 2021)
  • [Good Friday] (Friday, April 2, 2021)
  • [Easter Monday] (Monday, April 5, 2021)
  • [National Patriots’ Day] (Monday, May 24, 2021)
  • [St. Jean Baptiste Day] (Thursday, June 24, 2021)
  • [Canada Day] (Thursday, July 1, 2021)
  • [Labour Day] (Monday, September 6, 2021)
  • [National Day for Truth and Reconciliation] (Thursday, September 30, 2021)
  • [Thanksgiving Day] (Monday, October 11, 2021)

7.5 Daily hours - Regular work week. 37.5 hours (5 x 7.5)

Nice to have would be employee salary

The minding here is consultants, so there is no salary, it’s more a
per hour billing and depending on many factors that per/hour $ can change.

All these éléments are not critical in my view. Only important if you want to figure out some
Utilization KPI and other KPI metrics.

2 Likes
5

Hi everyone,

I would also like to add a little something to the data, if possible.

About the utilization rate: I was going with this link https://www.smartsheet.com/content/what-is-utilization-rate-formula - the page calculates with 2,000 working hours a year.

Also I made an asumption of 8 hours of work per day. This is the normal working time in Austria (where I am from).

With so little data, assumptions have to be made. I am pretty sure EDNA is aware of that :wink:

1 Like
6

All,

FWIW, I have brought in additional data in almost every one of my past challenge entries, but just made sure to document that in my writeup. I do it for two reasons: 1) to best mimic what I would do in a real-life situation; and 2) to further my own learning goals for that challenge.

Keep in mind that the primary audience for your challenge entry is yourself. Try to hew to the general outline of the brief, but feel free to do what you need to do to make the exercise maximally useful for your own learning.

FYI - within the US government, the standard assumption for budgeting one FTE (full-time employee) workyear is 2080 hours.

  • Brian
5 Likes
7

8 hours or 7.5 hours no big difference, but keep in mind that this is for consultant not full time employee’s so one consulting contract might different from the next. As you can see in the data, different customers and different hourly rate $. Also, some gaps in the timeline. You might have some time where you don’t have work or a customer between the contracts.

So, if I would to a Utilization KPI I would and I probably calculate it on a monthly basis, not yearly. Then take all my monthly’s utilization and make a yearly Utilization KPI

Also, you will notice a gap in the data, 2020-04 to 2020-08 that’s when COVID-19 hit our part of the world and everything was shut down.

1 Like
8

@Sabine, @ericet , @KimC
The en-US version of Microsoft Project provide a Standard Base Calendar that reflects a traditional work schedule in US as Monday through Friday, 8:00 A.M. to 5:00 P.M., with an hour off for break that is an availability 08 hours per working day
and you can find the 10 days US Federal Holidays here US Holidays 2020 (us-public-holidays.com)
This is how you can get the 2000 working hours mentioned in the resource provided in the brief

9

Yes, that is correct and does not make a big difference and might have some differences around the world.

As for the utilization KPI the more I think about it the more it will be based on monthly hours. Still thinking about that…

1 Like
10

All,

For those who want to add a Quebec public holiday table to the Dates table provided to aid in the calculation of utilization rates, I’ve done so by iterating on the following website for 2018-2020, and then sending the results out to CSV via DAX Studio.

If you don’t want to go through the minor headache of doing this, I’ve attached my CSV file below. Note that when you pull the attached file in via Get Data, you’ll just need to strip off the time string and change the Holiday Date field from text to date type.

Good luck with your entries! I’m really enjoying this one…

  • Brian

Quebec Public Holidays 2018-2021.csv (1.5 KB)

2 Likes
11

Hello All,

I decided to add another column (Hours per Day) to the numbers-export-atwork.csv dataset.

This will permit me to add the Hours per Day to the data
not all contract have the same hours per day value.
In this dataset the values range is: 7, 7.5, 8 hours per day.

With this value, I will now be able to calculate my Utilization KPI

Utilization = (Billable Hours / Total Hours) x 100 %

Here is the new dataset with the new Hours per Day column.
numbers-export-atwork.csv (51.7 KB)

Change Columns=12 to Columns=13
in the Power Query Editor for the Source

= Csv.Document(File.Contents(“C:\numbers-export-atwork.csv”),[Delimiter=“;”, Columns=13, Encoding=1252, QuoteStyle=QuoteStyle.None])

12

Challenge16_Resource_timesheet_utilization_Screenshot 2021-09-20
Challenge16_Resource_timesheet_utilization_Screenshot 2021-09-20910Ă—511 127 KB

I accepted the challenge and sharing the image of my report…

I have exposure to sales related data only. This resource utilization helped me to learn many things especially understanding the data first. I went through many articles on this for basic understanding and then decided what are all things a user may wish to view in the dashboard.

I am participating for the first time. I need your input to fine tune

5 Likes
13

I like it, fun to see other ideas. I don’t understand the profit. It’s a timesheet with yes $ values for the hour * by hourly rate which give the total billed revenue for a month example not profit.

Great work !

14

I assumed that the average hourly rate is derived considering a minimum of 25% profit. When the utlization rate goes down, it will impact our profit. Considering that, I have included how much we are getting.

Please advise

15

My point of view is that this dashboard is not made for Profit tracking it’s mainly to track a consultants hours at a customer with the related revenue generated by the consultants hours that he has done.

Also for the utilization KPI is to give an indication of how many potential hours their is and how many hours he did work out of the potential hours he could have done.

16

Yes, I understood sir !!
Regards
S Uma

17

I made a small mistake here is the updated file

numbers-export-atwork.csv (52.0 KB)

19

The error is just one of the customers-client had 7 hour per day instead of 7.5 per day so I realized it the total for that customer was not what I expected. But if your using my file where I added a field then just reload it if you did not use my file then it’s Ok

I added a field to be able to calculate the utilization KPI.

Looking forward to seeing your submission

1 Like
20

I have a question for the experts, something that is bugging me for my report. When I start the sorting is good, when I do a drill down I don’t like the sort. I’m drilling down from year to month, year is Ok but when I drill down to months, the months are not in chronological order… and that bugs me.

22

What a fun project! I focused on utilization and I took a simple approach to the utilization target.
50 weeks in a year, allowing for two weeks of vacation and holidays. 40 hours per week gives 2000 hours per year. I took 90% to allow for administration, training, and sales activities. So, the utilization target is 1800 hours per year.

I cleaned up the data a bit and removed duplicates in the model.

For the report, I took an opportunity to use the AI visuals. I learned the AI visuals do not work in PBI Service, public web. You’ll see what I am talking about in the report link:
Link: https://app.powerbi.com/view?r=eyJrIjoiOGY3OTcwMGEtYTNlZC00ODU2LTk0YjctZGU1MzFhN2E5NjQ4IiwidCI6IjQ1NzFjNmIzLTlhNTQtNGNhNC1iZWYwLTUwNDQyMTdhYTg2NSIsImMiOjJ9

What do you think of the result?

image
image1601Ă—899 131 KB

image
image1599Ă—916 114 KB

image
image1599Ă—897 85.6 KB

Diana

6 Likes