cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
wkeicher Regular Visitor
Regular Visitor

Computing Resource Utilization Per Day, Week and Month

I am trying to calculate Resource utilizatio taking into accoun the following:

What I have:

  • Resource timesheets by date and # of hours
  • Calendar of a fiscal 2019 year by day (Day of week Name)
  • Table of Holidays

What I am trying to accomplish:

  • Measure Utilization for each employee by day/week/month
  • eliminate weekends and holidays from available utilization unless:
    • The employee worked on a weekend or holiday (i.e. has time entry in timesheet)
    • If worked holiday or weekend  - Actual Hours entered =actual hours availabe (i.e. 100% utilization)

Thank you..!

2 ACCEPTED SOLUTIONS

Accepted Solutions
wkeicher Regular Visitor
Regular Visitor

Re: Computing Resource Utilization Per Day, Week and Month

I thought I would be able to limit the hours by way of filter in the GUI? Is that incorrect?

Utilization is based on specific time entries (Soem projects are billable, some are not). My thought was to filter out the non billable work with Report/Page/Visual Filters. Is that not possible?

 

 

natelpeterson New Contributor
New Contributor

Re: Computing Resource Utilization Per Day, Week and Month

You will want to multiply the available hours by the number of employees within the filter context:

var num_selected_employees = DISTINCTCOUNT('Users'[UserID])
var workday_available_hours = COUNTROWS(FILTER('Date',AND('Date'[Workday] = "Yes", 'Date'[DateGroup] ="Past")) * 8 * num_selected_employees 

 Cheers!

Nathan

15 REPLIES 15
natelpeterson New Contributor
New Contributor

Re: Computing Resource Utilization Per Day, Week and Month

@wkeicher - Try the following:

1. Add holidays to your date table, and also have a weekend flag, and finally combine the two to create a workday flag.

2. Create a measure like this:

Util = 
var workday_available_hours = COUNTROWS(FILTER('Date','Date'[Workday] = "Yes")) * 8
var workday_worked_hours = CALCULATE(SUM('Timesheets'[Hours]), 'Date'[Workday] = "Yes")
var non_workday_worked_hours = CALCULATE(SUM('Timesheets'[Hours]), 'Date'[Workday] = "No") 
return DIVIDE(
  workday_worked_hours + non_workday_worked_hours,
  workday_available_hours + non_workday_worked_hours 
)

Hope this helps,

Nathan

wkeicher Regular Visitor
Regular Visitor

Re: Computing Resource Utilization Per Day, Week and Month

Thank you. need a bit more assistance.

 

My Date Table is created as follows:

Working Dates =
ADDCOLUMNS (
CALENDAR (DATE(2019,1,1), DATE(2019,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)
 
How can I set the appropriate values for Holidays, Weekend, and a workday Flag?
 
Holidays are as follows: (However I have a Holiday table with just these dates.
20190101
20190218
20190419
20190527
20190704
20190705
20190902
20191014
20191128
20191129
20191225
 
natelpeterson New Contributor
New Contributor

Re: Computing Resource Utilization Per Day, Week and Month

@wkeicher - Try this. It assumes you have a table "Holiday" with column "DateAsInteger":

Working Dates = 
var cal = ADDCOLUMNS (
    CALENDAR (DATE(2019,1,1), DATE(2019,12,31)),
    "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ) * 1,
    "Year", YEAR ( [Date] ),
    "Monthnumber", FORMAT ( [Date], "MM" ),
    "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ),
    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
    "DayOfWeekNumber", WEEKDAY ( [Date] ),
    "DayOfWeek", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)
var cal_with_flags = ADDCOLUMNS(
    cal,
    "IsHoliday", var a = LOOKUPVALUE(Holiday[DateAsInteger], Holiday[DateAsInteger],[DateAsInteger], BLANK())
                return IF(ISBLANK(a),"No","Yes"),
    "IsWeekend", IF([DayOfWeekNumber] IN {1,7}, "Yes", "No")
)
return ADDCOLUMNS(
    cal_with_flags,
    "IsWorkday", IF(OR([IsHoliday] = "Yes",[IsWeekend] = "Yes"),"No", "Yes")
)

Cheers!

Nathan

wkeicher Regular Visitor
Regular Visitor

Re: Computing Resource Utilization Per Day, Week and Month

This is awesome, however serach value issue. My Column_Name in Holidays Table is Holiday_Date5-19-2019 5-31-52 PM.png 
natelpeterson New Contributor
New Contributor

Re: Computing Resource Utilization Per Day, Week and Month

@wkeicher - The underlined should be [DateAsInteger]

wkeicher Regular Visitor
Regular Visitor

Re: Computing Resource Utilization Per Day, Week and Month

Hey Nathan - Yeah I figured that out. So my calendar table looks good. I joined that table with my timesheets table and applied the Util Measure you provided previously. I probably left out some details, however my timesheets table has entries going back to 2014, my calendar table is just for 2019. Not sure if that matters, as I have the two joined by date.  My goal is to reveal utilization by day/week/month per resource/group. It seems you the util Measure is overstating utilization.

 

A few observations:

  • date Hierarchy is no longer auto grouping by Day, Month, Quarter, Year
  • Util % is over stated - I added report level filters that I though would have also affected the UTIL measure, but it doesnt appear to be changing

5-20-2019 8-57-08 AM.png

 

By the way - really appreciate your help...!

 

Thanks,

Wayne

 

natelpeterson New Contributor
New Contributor

Re: Computing Resource Utilization Per Day, Week and Month

@wkeicher  - 

1. I'm not sure what you mean about the hierarchy. What's happening incorrectly there?

2. How do you define Utilization? Is it Billable / Total Hours on TimeSheet, or Billable / Hours in selected dates? Or something else?

If it's the 2nd, you'll need to limit the hours to Billable only. I'd previously thought that the table was only considering Billable. Again, your measure will depend on your data and how you want to calculate it.

Util = 
var workday_available_hours = COUNTROWS(FILTER('Date','Date'[Workday] = "Yes")) * 8
var workday_worked_hours = CALCULATE(SUM('Timesheets'[Hours]), 'Date'[Workday] = "Yes", 'Timesheets'[Billable] = "Yes")
var non_workday_worked_hours = CALCULATE(SUM('Timesheets'[Hours]), 'Date'[Workday] = "No") 
return DIVIDE(
  workday_worked_hours + non_workday_worked_hours,
  workday_available_hours + non_workday_worked_hours 
)

Cheers,

Nathan

wkeicher Regular Visitor
Regular Visitor

Re: Computing Resource Utilization Per Day, Week and Month

I thought I would be able to limit the hours by way of filter in the GUI? Is that incorrect?

Utilization is based on specific time entries (Soem projects are billable, some are not). My thought was to filter out the non billable work with Report/Page/Visual Filters. Is that not possible?

 

 

natelpeterson New Contributor
New Contributor

Re: Computing Resource Utilization Per Day, Week and Month

@wkeicher - Yes, it's possible to filter as you describe. I don't know why the calculation is returning an unexpected result. You could create other measures based on the variables for debugging. For example:

workday available hours = COUNTROWS(FILTER('Date','Date'[Workday] = "Yes")) * 8

Then see whether any of those values are incorrect. Please let us know the results.

Cheers,

Nathan