Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wkeicher
Helper III
Helper III

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

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?

 

 

View solution in original post

Anonymous
Not applicable

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

View solution in original post

16 REPLIES 16
Anonymous
Not applicable

@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

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
 
Anonymous
Not applicable

@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

This is awesome, however serach value issue. My Column_Name in Holidays Table is Holiday_Date5-19-2019 5-31-52 PM.png 
Anonymous
Not applicable

@wkeicher - The underlined should be [DateAsInteger]

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

 

Anonymous
Not applicable

@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

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?

 

 

Anonymous
Not applicable

@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

Hi Nathan,

 

So, I tried troubleshooting by breaking down the measures like you suggested. I think the issue has to do with the relationship between the timesheets(ActivityUpdates) table, and the Calendar(WorkingDates)Table. The Sum of all hours entered in timesheets is correct on my report, however the Util Measure appears to only calculate availabe hours for days that time was entered, therefore utilization looks high on all resources.

 

Example:

The highlighted resource only worked 313 (reg Hours), and 7 (Ot Hours) from 1/1/2019 thru Today. Available hours from 1/1/2019 thru Today should be upward of 750+(the 7OT) = 757 hours. Utilizatiuon would be appr0x 42%5-21-2019 7-39-09 AM.png5-21-2019 7-33-36 AM.png

 

Anonymous
Not applicable

@wkeicher  - Try removing the bi-directional filtering. Typically, you want Dimension tables (such as Date, Product, Employee, etc) to filter Fact tables (such as Sales, Timesheets, etc) and do not let fact tables filter dimension tables.

Hope this helps,

Nathan 

Getting close...The Utill (Available_Hours) looks like it is showing full year.5-21-2019 8-34-27 AM.png

Anonymous
Not applicable

@wkeicher  - It should work if you apply filter(s) from the calendar. This can be done with a Filter/Slicer on the report. It can also be applied within the Measure, although that can limit flexibility.

I like to use "Relative" and "Group" columns in my Date dimension. (See modified script below). You could add a filter/slicer to your report, or you could add a filter in your Measure, like this:

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

Enhanced Date Dimension:

 

Working Dates = 
var datetoday = TODAY()
var yeartoday = YEAR(datetoday)
var cal = ADDCOLUMNS (
    CALENDAR (DATE(yeartoday - 2,1,1), DATE(yeartoday,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 cal2 = ADDCOLUMNS(
    cal,
    "IsHoliday", var a = LOOKUPVALUE(Holidays[Holiday_Date], Holidays[Holiday_Date],[DateAsInteger], BLANK())
                return IF(ISBLANK(a),"No","Yes"),
    "IsWeekend", IF([DayOfWeekNumber] IN {1,7}, "Yes", "No"),
    "RelativeDay", DATEDIFF(datetoday,[Date], DAY),
    "RelativeWeek", DATEDIFF(datetoday,[Date], WEEK),
    "RelativeMonth", DATEDIFF(datetoday,[Date], MONTH),
    "RelativeQuarter", DATEDIFF(datetoday,[Date], QUARTER),
    "RelativeYear", DATEDIFF(datetoday,[Date], YEAR)
)
var cal3 = ADDCOLUMNS(
    cal2,
    "IsWorkday", IF(OR([IsHoliday] = "Yes",[IsWeekend] = "Yes"),"No", "Yes"),
    "DateGroup", SWITCH(TRUE(),[RelativeDay]<0,"Past",[RelativeDay]>0,"Future","Today")
)
return cal3

 

 

Awesome - Util % is working...! 

 

Last and Final question. My Resources are in groups and the Group Line is summing the %Util, as well as the Hours. Would like the Group Util% to be an aggragate percentage of utilization for the group, yet keep the Regular and OT hours as a sum. Hope this makes sense.

 

5-21-2019 9-51-30 AM.png

Hello my friend,

Do you mind sharing your datasource template?

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.