Forum Discussion

wkeicher's avatar
wkeicher
Helper III
7 years ago
Solved

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..!

Need Help
  • wkeicher's avatar
    wkeicher
    7 years ago

    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's avatar
    Anonymous
    7 years ago

    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

16 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      wkeicher
      Helper III
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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