Calculating Resource Utilization (Project Online)

[jeremycolson]

Hi everyone:

I'm relatively new to PowerBI, but I've read all the courses online (great job Microsoft), created a dozen dashboards, and feel I have a basic understanding...  What I'm trying to do seems fairly simple to me, but I cannot, for the life of me, figure out to accomplish it.

The Goal

I'm trying to calculate the billable utilization of resources based on timesheet entries

The How

To keep it simple, imagine there's a table on the dashboard that lists resources (resource name, sum(billable hours), sum(non billable hours), sum(total hours)).  Also on this dashboard is a OOTB slicer based on TimeOfDay so users can filter the dataset down to the timeframe that interests them.  Pretty basic, the user picks a timeframe, and the table shows the resources and their billable and non-billable hours.  

Where I'm Stuck

Now I want to dynamically calculate and show the billable utilization of each resource in the table based on the slicer.  The math itself is fairly simple.

Billable Utilization (%) = (Utilized Hours/Available Hours)*100

I start with a standard constant: there are 1980 billable hours available in a calendar year.  To calculate the available hours for the time period selected, I need to calculate the number of days between T1 (filter start date) and T2 (filter end date).  This I cannot figure out.  Once I have the numbers of days between T1 and T2, I can calculate how many of the 1980 billable hours apply for the selected time period.  

Available Hours = (T2-T1)*(1980/365)

With this estimated, I can then calculate the billable utilization of each resource...

Billable Utilization (%) = (Billable Hours/Available Hours)*100%

I've banged my head against the wall for a few evenings now trying to figure this out and would greatly apprecaite any help anyone can offer.

Thanks so much for your help.

-JC

[Vvelarde]

@jeremycolson

hi, try with this and let me know 

[jeremycolson]

Hi @Vvelarde and @parry2k:

Thanks for the input.  I unfortunately tried this solution a few days ago.  The problem is that it does not return the filter dates, but rather the min/max dates for the dataset within the filter dates.  As an example, let's say our dataset consists of user, date, hours.

user        date               hours

john       12/01/2016    4 hrs

john       12/02/2016    4 hrs

john       12/03/2016    4 hrs

john       12/04/2016    4 hrs

john       12/05/2016    4 hrs

jane       12/03/2016     4 hrs

jane       12/04/2016     4 hrs

Using the filter, assume we select the period 12/01/2016-12/05/2016 (date format: mm/dd/yyyy).

NumberOfDays is calculated as 5 for John, but only 2 for Jane (at least in my dashboard).  This would then affect the utilization calculation for Jane artifically inflating the number.  In order to ensure the calculation is accurate, I need to use the same NumberOfDays for everyone based on the dates selected using the filter.  Maybe I'm using the measure incorrectly?

-Jeremy

[jeremycolson]

I should note, there are two primary underlying tables in Project Online I need to use for this... one that contains all the resources (resource_id, resource_name, etc.) and another that contains the timesheetdataset (resource_id, date, billable_hours, etc.).  I created the measure for the NumberOfDays in the timesheetdataset (where the date is located).  However, when I utilize this measure in the resources table (where I need to create another measure to calculate the billable utilization for each resource), I get varying NumberOfDays based on the timesheet time entry dates for the given user within the specified filter dates.  Sorry... just for clarity.

[parry2k]

@jeremycolson I can see where the poblem is. To work what @Vvelarde you need continuate date table, and use date column from date table as a slicer and apply @Vvelarde on that date table. Also you need to set relation between your timsheetdaaset with date table and that will fix the issue.

Let me know if you need further help on how to achieve all this?

Here in summary what you need:

- add date table

- set relation between timesheetdataset with your date table

- add numberofdays formula in date table

- put date column from date table as a slicer

Hope it is helpful.

Thanks

Parv

[parry2k]

Here are few links on how to create date table in PowerBI using DAX:

http://www.agilebi.com.au/power-bi-date-dimension/

http://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-steps...

https://msdn.microsoft.com/en-us/library/dn802546.aspx

[behnamb]

Hi @Vvelarde and @parry2k:

I also have a pretty similar problem for calculating employee utilization. I would appreciate it if anyone could help.

I use Jeremy's example to explaine mine. Let's assume we have the following table for ActualHours:

User        Date            Actual Hours

john       12/01/2016    4 hrs

john       12/02/2016    4 hrs

john       12/03/2016    4 hrs

john       12/04/2016    4 hrs

john       12/05/2016    4 hrs

jane       12/03/2016     4 hrs

jane       12/04/2016     4 hrs

I also have another Calender_Lookup table for dates:

date               WorkingHoursPerDay

12/01/2016           8 hrs

12/02/2016           8 hrs

12/03/2016           8 hrs

12/04/2016           8 hrs

...

12/30/2016           8 hrs

12/31/2016           8 hrs

Now, to calculate the utilization, I'd like to get the summuation of Actual Hours from top table divided by the sumuation of Avialable Hours in bottom table using following measure:

Utilization = DIVIDE(
                         SUM(ActualHours[Actual Hours])

                         /
                         DISTINCTCOUNT(ActualHours[User]),

                         CALCULATE(
                                SUM(Calander_Lookup[WorkingHoursPerDay]),
                                FILTER(
                                      ALL(Calander_Lookup),
                                      Calander_Lookup[Date] >= DATE(
                                                                                      YEAR(FIRSTDATE(ActualHours[Date])),

                                                                                      MONTH(FIRSTDATE(ActualHours[Date]))

                                                                                      ,1
                                                                                   ) &&
                                      Calander_Lookup[Date] <= EOMONTH(
                                                                                      LASTDATE(ActualHours[Date]),0
                                                                                   )
                                )
                         )

                   , 0
                  )

I was hoping the get a result like this:

Utilization for each User = Sum(ActualHours) / Sum(AvailableHours in Period)

For example: John  =  16 / (31*8) = 6.5%

But my measure returns 16 / (5*8) = 40%

Although I tried to tell the system getting the first date of the month in Calender_Lookup table and sum up the WorkingHours for the entire month, the measure ignores it and only sums up the rows that there is a match in ActualHours table. 

Could you please help me find a solution for it?

Many thanks inadvance,

Behnam

[behnamb]

I found the issue with the script and here is the corrected one for those how may have a similar problem:

Utilization = DIVIDE(
                         SUM(ActualHours[Actual Hours])

                         /
                         DISTINCTCOUNT(ActualHours[User]),

                         CALCULATE(
                                SUM(Calander_Lookup[WorkingHoursPerDay]),
                                FILTER(
                                      ALL(Calander_Lookup),
                                      Calander_Lookup[Date] >= DATE(
                                                                                      YEAR(MIN(ActualHours[Date])),

                                                                                      MONTH(MIN(ActualHours[Date]))

                                                                                      ,1
                                                                                   ) &&
                                      Calander_Lookup[Date] <= EOMONTH(
                                                                                      MAX(ActualHours[Date]),0
                                                                                   )
                                )
                         )

                   , 0
                  )

[parry2k]

Just to further clarify @Vvelarde solution, he added a measure called "NumberofDays" using DAX formula. Reason I wanted to put it here so that @jeremycolson has better understanding what is the solution.