Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to calculate Resource utilizatio taking into accoun the following:
What I have:
What I am trying to accomplish:
Thank you..!
Solved! Go to Solution.
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?
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
@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:
@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
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:
By the way - really appreciate your help...!
Thanks,
Wayne
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?
@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%
@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.
@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.
Hello my friend,
Do you mind sharing your datasource template?
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
User | Count |
---|---|
94 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |