Forum Discussion

jeremycolson's avatar
jeremycolson
New Member
9 years ago

Calculating Resource Utilization (Project Online)

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

 

Need Help

8 Replies

    • parry2k's avatar
      parry2k
      Super User
      9 years ago

      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.

    • jeremycolson's avatar
      jeremycolson
      New Member
      9 years ago

      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's avatar
        jeremycolson
        New Member
        9 years ago

        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.