Latest Enterprise DNA Initiatives

Challenge 16 - Timesheet Utilisation Reporting

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.

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)

9 Likes

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

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

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

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

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
4 Likes

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

@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

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

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)