I’ve been dealing with professional services companies for a while. Every one of them wants to 1. track time and 2. report on how utilized their team is. Regardless of the tool they use to track time (I’ve used Dynamics AX 2009, Dynamics AX 2012, Toggl, Dynamics 365 Project Service Automation, Dynamics 365 for Finance and Operations, and now Dynamics 365 Project Operations), their reporting needs are pretty similar.
When it comes to building a report and structuring a KPI, my first question is always “what are you trying to understand from this information?” In my opinion, understanding a team’s utilization allows me to compare members to themselves over time and also compare themselves to each other (or a team to another team). Some companies use a utilization metric for variable incentives, and so their need might be slightly different than mine (maybe they have to exclude PTO or company holidays), but I don’t and I like to keep my calculation as simple as possible.
Utilization overview
To me, utilization = the number of billable hours worked in a certain time period / the number of available hours in the same time period. There are some basic utilization calculations inside of Dynamics 365 Project Operations, but I like to build my reporting in Power BI, so that’s what I’m going to do.
Your first thought might be to sum up all the tracked time to get the total hours available and then sum up all the time marked as billable to get the numerator. What this assumes, though, is that everyone is tracking exactly 8 hours a day (or whatever your standard day is). This doesn’t work, obviously, as some people work more than that and sometimes people don’t put all their time in the system.
Approach
What I like to do is create a date table that has a row for every date in the calendar – not based on the fact table. I then cross join that table to my employee table (to get a combined table of employees and all the dates in the calendar). Finally, I create a measure that takes all that information and then sums the billable hours by that date by employee.
I forget how to do this correctly every time, so I asked ChatGPT and Claude. Out of the two, I feel like I got better responses from ChatGPT.
Here are some of the prompts I used:
- taking a look at this, i want to combine temp and temp2 into a single table that has a single “billable hours” and “availalble hours” column for each date / bookableresourcename combination {code snippet}
- assuming you know what you received above, is there a better way to do this?
- it’s really close, but i need a record for each of the rows in the transaction date table. the way that you’ve done it above only has a record if there is a record in the time entries table.
- No common join columns detected. The join function ‘NATURALLEFTOUTERJOIN’ requires at-least one common join column.
After a lot of back and forth, I ended up settling on creating a calculated table and an additional calculated measure.
Utilization =
SUMMARIZE(
CROSSJOIN(
'transaction date',
VALUES('Time entries'[msdyn_bookableresourcename])
),
'transaction date'[Date],
'Time entries'[msdyn_bookableresourcename],
"Is Workday", IF(WEEKDAY('transaction date'[Date], 2) <= 5, TRUE(), FALSE()),
"Available Hours", IF(WEEKDAY('transaction date'[Date], 2) <= 5, 8, 0),
"Billable Hours",
COALESCE(
CALCULATE(
SUM('Time entries'[msdyn_duration]) / 60, 'Time entries'[msdyn_transactioncategoryname] = "Billable",
USERELATIONSHIP('transaction date'[Date], 'time entries'[msdyn_date])
),
0
)
) ChatGPT generated code that summarizes a table based on the CROSSJOIN of my dates table and the bookable resources name from my time entries table. It then adds a column indicating if the date is a workday (and should be included in the available hours) and then adding 8 available hours if it’s a workday.
Next, it calculates the billable hours by looking at my time entries table for records that were marked as billable and puts a 0 in if there are no billable hours.
After this calculated table was created, I created a calculated measure to calculate the utilization percent. Using a calculated measure allows utilization to calculate properly at all levels of aggregation, as well.
Utilization % =
VAR TotalAvailableHours = CALCULATE(
SUM('Utilization'[Available Hours]),
USERELATIONSHIP('Utilization'[msdyn_bookableresourcename], 'Bookable resource'[name])
)
VAR TotalHoursWorked = CALCULATE(
SUM('Utilization'[Billable Hours]),
USERELATIONSHIP('Utilization'[msdyn_bookableresourcename], 'Bookable resource'[name])
)
RETURN
IF(
TotalAvailableHours > 0,
DIVIDE(TotalHoursWorked, TotalAvailableHours),
BLANK()
)Ignore the USERELATIONSHIP piece for a second. This calculated measure is pretty simple. It checks to ensure the total available hours are greater than zero and then divides the total billable hours by the total available hours. The reason I put it as a separate measure is to make sure the totals look right when thrown in a chart or a matrix report.
The USERELATIONSHIP section is just a way for me to link this table to the bookable resource table so I can filter my reports using the dimension vs. the fact table. I have so many relationships I had to use an inactive relationship between these two tables, and the USERELATIONSHIP allows me to specify it for just this measure.
Parting thoughts
We can make this more complicated, obviously. If you have a holiday calendar, you can use it to augment the working days. Similarly, if you have a way of identifying PTO (either though your timesheet system or some additional system), you can use that to remove numbers from your available working hours.
The calculation I referenced above works for me. It allows me to get a trend of my billable hours (and allows me to infer revenue) and lets me look across practices for some like-for-like comparisons.
Oh, and if you need a good calculated date table, this one from radacad is where I always start.
Leave a Reply