Include Employee start and end date in Utilization %

[wkeicher]

I am calcualting Employee project utilization from Time sheet data and calendars that identifyy a work day and non work day, however, i need to now adjust utilization for terminated employees as thay are no longer available. I have a start date and end date for each employee (Blank end date  if Still Working). How would I go about determing utilization for each employee based on termination date?

Util =

var num_selected_employees = DISTINCTCOUNT('Users'[Name])

var workday_available_hours = COUNTROWS(FILTER('Working Dates','Working Dates'[IsWorkday] = "Yes")) * 7.5 * num_selected_employees

var workday_worked_hours = CALCULATE(SUM('ActivityUpdates'[Hours]), 'Working Dates'[IsWorkday] = "Yes")

var non_workday_worked_hours = CALCULATE(SUM('ActivityUpdates'[Hours]), 'Working Dates'[IsWorkday] = "No")

return DIVIDE(

workday_worked_hours + non_workday_worked_hours,

workday_available_hours + non_workday_worked_hours

)

[Anonymous · Accepted solution]

Utilization =
-- Each user must have a unique name.
-- If this is not the case, you should
-- use a field that uniquely identifies
-- a user. Such a field should be marked
-- in Power BI as a row identifier.
var __visibleUsers = 
	SUMMARIZE(
		Users,
		Users[Name],
		Users[StartDate],
		Users[TerminationDate]
	)
var __numberOfWorkingHoursADay = 7.5
var __utilization =
	AVERAGEX(
		__visibleUsers,
		var __userStartDate = Users[StartDate] 
		var __userEndDate = 
			if(
				Users[TerminationDate] = BLANK(),
				date(2099, 1, 1),
				Users[TerminationDate]
			)
		var __totalPossibleWorkingDays =
			COUNTROWS (
				FILTER (
				    'Working Dates',
				    'Working Dates'[IsWorkday] = "Yes",
			            KEEPFILTERS( 'Working Dates'[Date] >= __userStartDate ),
			            KEEPFILTERS( 'Working Dates'[Date] <= __userEndDate ),
				)
			)
		var __totalPossibleWorkingHours =
			__totalPossibleWorkingDays * __numberOfWorkingHoursADay
		var __totalMandatoryWorkedHours =
		    CALCULATE (
		        SUM ( 'ActivityUpdates'[Hours] ),
		        'Working Dates'[IsWorkday] = "Yes"
		    )
		var __totalOvertimeWorkedHours =
		    CALCULATE (
		        SUM ( 'ActivityUpdates'[Hours] ),
		        'Working Dates'[IsWorkday] = "No"
		    )
		var __totalWorkedHours =
			__totalMandatoryWorkedHour + __totalOvertimeWorkedHours
		var __totalPossibleWorkingHours =
			__totalPossibleWorkingHours + __totalOvertimeWorkedHours
		return
			divide(
				__totalWorkedHours,
				__totalPossibleWorkingHours
			)
	)
RETURN
	__utilization

Best

Darek

[Anonymous]

From your post it's not really clear what you're asking for...

Best

Darek

[wkeicher]

i need to include additional criteria in my Utilization % Calculation that takes into account an employees tenure. In other words if they were active employees or not. A terminated employee should no longer effect utilization as of the termination date. If an employee was hired on 1/1/2019 and was terminated on 6/30/2019, and during  that time he worked 8 hours a day, he would be 100% utilized for that period. However, he would only be 50% utilized for all of 2019, because he was terminated on 6/30 (This is incorrect). Neeed assistance in incorprating termination date into the dax calculation for utilization.

Hope you can undestand this.

---

[Anonymous]

OK, for one employee the calculation is understandable. But what algorithm would you give for the calculation of utilization of a set of employees? Say you have 2 emps and the period is 1 year. Employee 1 worked fully for the first half of the year only. The other worked full-time for 3/4 of the year. What would be the total utilization for the two workers together?

If I were to define it, it would be something like:

( 1/2 + 3/4 ) / 2 = 5 / 8

Would you agree? If this is so, then the algorithm would be: For each employee calculate the ratio of utilization (it must be between 0 and 1, inclusive) in the selected period and then average over the employees.

Well, what do you say?

Best

Darek

[wkeicher]

Yes that sounds correct.

Bottom line is utilization is calculated at the employee level, and aggregated at the team level. So if employee 1 has 100% utilization for 6 months, and employee 2 has 50% utilization for 1 year, the aggregate utilization for the year would be 75%.

So yes I agree. I just need help including termination date into the utilization calculation.

[Anonymous]

Well, this is rather not that difficult... Iterate over the visible employees, calculate their utilization for the selected period of time and the aggregate with AVERAGEX. The utilization for the period for one employee can be calculated via this algorithm:

1. Find the total possible hours that could have been worked (T).

2. For any particular employee, find the total official hours worked by the employee (ET).

3. Then find the overtime in the period (EOv) for the employee in question.

4. Then the utilization for her/him would be U = ( ET + EOv ) / (T + EOv).

5. Average U over all visible employees.

You could also do something different:

1. Find the total possible hours that could have been worked (T) plus total possible hours of overtime (Ov).

2. For any particular employee, find the total official hours worked by the employee (ET).

3. Then find the overtime in the period (EOv) for the employee in question.

4. Then the utilization for her/him would be U = ( ET + EOv ) / (T + Ov).

5. Average U over all visible employees.

You have to decide which algorithm expresses better what you want... By the way, there's no need to be concerned about termination dates. The above calculations take them into account automatically through the calculation of working hours of the employees.

Best

Darek

[wkeicher]

Ok So I'm a bit new at DAX. What would a dax formula look like to:

11. Find the total possible hours that could have been worked (T).

2. For any particular employee, find the total official hours worked by the employee (ET).

3. Then find the overtime in the period (EOv) for the employee in question.

4. Then the utilization for her/him would be U = ( ET + EOv ) / (T + EOv).

5. Average U over all visible employees.

I've included my current DAX Expression - just need to incorprate Hire date and termonation date

Util =

var num_selected_employees = DISTINCTCOUNT('Users'[Name])

var workday_available_hours = COUNTROWS(FILTER('Working Dates','Working Dates'[IsWorkday] = "Yes")) * 7.5 * num_selected_employees

var workday_worked_hours = CALCULATE(SUM('ActivityUpdates'[Hours]), 'Working Dates'[IsWorkday] = "Yes")

var non_workday_worked_hours = CALCULATE(SUM('ActivityUpdates'[Hours]), 'Working Dates'[IsWorkday] = "No")

return DIVIDE(

workday_worked_hours + non_workday_worked_hours,

workday_available_hours + non_workday_worked_hours

)

[wkeicher]

I think I need to expand on this variable to account for start and end date of an employee...

var workday_available_hours = COUNTROWS(FILTER('Working Dates','Working Dates'[IsWorkday] = "Yes")) * 7.5 * num_selected_employees

[Anonymous]

I don't know the model but if the model is right, you'd do something like this:

Utilization =
-- Each user must have a unique name.
-- If this is not the case, you should
-- use a field that uniquely identifies
-- a user. Such a field should be marked
-- in Power BI as a row identifier.
var __visibleUsers = VALUES( Users[Name] )
var __numberOfWorkingHoursADay = 7.5
var __totalPossibleWorkingDaysForOneUser =
	COUNTROWS (
		FILTER (
			'Working Dates',
			'Working Dates'[IsWorkday] = "Yes"
		)
	)
var __totalPossibleWorkingHoursForOneUser =
	__totalPossibleWorkingDaysForOneUser
		* __numberOfWorkingHoursADay
var __utilization =
	AVERAGEX(
		__visibleUsers,
		var __totalMandatoryWorkedHours =
		    CALCULATE (
		        SUM ( 'ActivityUpdates'[Hours] ),
		        'Working Dates'[IsWorkday] = "Yes"
		    )
		var __totalOvertimeWorkedHours =
		    CALCULATE (
		        SUM ( 'ActivityUpdates'[Hours] ),
		        'Working Dates'[IsWorkday] = "No"
		    )
		var __totalWorkedHours =
			__totalMandatoryWorkedHours
				+ __totalOvertimeWorkedHours
		var __totalPossibleWorkingHours =
			__totalPossibleWorkingHoursForOneUser
				+ __totalOvertimeWorkedHours			
		return
			divide(
				__totalWorkedHours,
				__totalPossibleWorkingHours
			)
	)
RETURN
	__utilization

Best

Darek

[wkeicher]

Hi Derek,

So this works great, however it is not taking into account the users start date and termination date.

Use Case 1:

So if I am Calculating utilization for a user who was terminated within the calendar period, i need to stop adding available work hours as of the termination date. 

Use Case 2:

If a user start within the period, I only want to start calculating utilization as of the start date andNOT include available hours prior to the start date.

My Users table has a StartDate and TerminationDate.

Thanks,

Wayne

[Anonymous · Accepted solution]

Utilization =
-- Each user must have a unique name.
-- If this is not the case, you should
-- use a field that uniquely identifies
-- a user. Such a field should be marked
-- in Power BI as a row identifier.
var __visibleUsers = 
	SUMMARIZE(
		Users,
		Users[Name],
		Users[StartDate],
		Users[TerminationDate]
	)
var __numberOfWorkingHoursADay = 7.5
var __utilization =
	AVERAGEX(
		__visibleUsers,
		var __userStartDate = Users[StartDate] 
		var __userEndDate = 
			if(
				Users[TerminationDate] = BLANK(),
				date(2099, 1, 1),
				Users[TerminationDate]
			)
		var __totalPossibleWorkingDays =
			COUNTROWS (
				FILTER (
				    'Working Dates',
				    'Working Dates'[IsWorkday] = "Yes",
			            KEEPFILTERS( 'Working Dates'[Date] >= __userStartDate ),
			            KEEPFILTERS( 'Working Dates'[Date] <= __userEndDate ),
				)
			)
		var __totalPossibleWorkingHours =
			__totalPossibleWorkingDays * __numberOfWorkingHoursADay
		var __totalMandatoryWorkedHours =
		    CALCULATE (
		        SUM ( 'ActivityUpdates'[Hours] ),
		        'Working Dates'[IsWorkday] = "Yes"
		    )
		var __totalOvertimeWorkedHours =
		    CALCULATE (
		        SUM ( 'ActivityUpdates'[Hours] ),
		        'Working Dates'[IsWorkday] = "No"
		    )
		var __totalWorkedHours =
			__totalMandatoryWorkedHour + __totalOvertimeWorkedHours
		var __totalPossibleWorkingHours =
			__totalPossibleWorkingHours + __totalOvertimeWorkedHours
		return
			divide(
				__totalWorkedHours,
				__totalPossibleWorkingHours
			)
	)
RETURN
	__utilization

Best

Darek

[wkeicher]

Error: Too Many Arguments Passed to the filter function, The Max is 2

[Anonymous]

Utilization =
-- Each user must have a unique name.
-- If this is not the case, you should
-- use a field that uniquely identifies
-- a user. Such a field should be marked
-- in Power BI as a row identifier.
var __visibleUsers = 
	SUMMARIZE(
		Users,
		Users[Name],
		Users[StartDate],
		Users[TerminationDate]
	)
var __numberOfWorkingHoursADay = 7.5
var __utilization =
	AVERAGEX(
	
		__visibleUsers,
		
		var __userStartDate = Users[StartDate] 
		var __userEndDate = 
			if(
				Users[TerminationDate] = BLANK(),
				date(2099, 1, 1),
				Users[TerminationDate]
			)
		var __totalPossibleWorkingDays =
			COUNTROWS (
				CALCULATETABLE(
					'Working Dates',
					'Working Dates'[IsWorkday] = "Yes",
			                KEEPFILTERS( 'Working Dates'[Date] >= __userStartDate ),
               			        KEEPFILTERS( 'Working Dates'[Date] <= __userEndDate )
				)
			)
		var __totalPossibleWorkingHours =
			__totalPossibleWorkingDays * __numberOfWorkingHoursADay
		var __totalMandatoryWorkedHours =
		    CALCULATE (
		        SUM ( 'ActivityUpdates'[Hours] ),
		        'Working Dates'[IsWorkday] = "Yes"
		    )
		var __totalOvertimeWorkedHours =
		    CALCULATE (
		        SUM ( 'ActivityUpdates'[Hours] ),
		        'Working Dates'[IsWorkday] = "No"
		    )
		var __totalWorkedHours =
			__totalMandatoryWorkedHour + __totalOvertimeWorkedHours
		var __totalPossibleWorkingHours =
			__totalPossibleWorkingHours + __totalOvertimeWorkedHours
		return
			divide(
				__totalWorkedHours,
				__totalPossibleWorkingHours
			)
	)
RETURN
	__utilization

You have to use CALCULATETABLE instead of FILTER.

Best

Darek

[wkeicher]

Hi Derek,

I think we're alomost there. See enclosed a Matrix Visual to assist in descibing the issue. There are 3 columns  - each represent Util calculations. As you can see the first 2 columns match, Col1 = my original utilization, Col2= your Utilization prior to incorporating StartDate & TermDates. Col3 =  is your latets version.

[wkeicher]

Here is your latest DAX - With a few updated I had to make regarding field names.

LatestUtil =

-- Each user must have a unique name.

-- If this is not the case, you should

-- use a field that uniquely identifies

-- a user. Such a field should be marked

-- in Power BI as a row identifier.

var __visibleUsers =

    SUMMARIZE(

        Users,

        Users[Name],

        Users[StartDate],

        Users[TerminationDate]

    )

var __numberOfWorkingHoursADay = 7.5

var __utilization =

    AVERAGEX(

        __visibleUsers,

        var __userStartDate = Users[StartDate]

        var __userEndDate =

            if(

                Users[TerminationDate] = BLANK(),

                TODAY(),

                Users[TerminationDate]

            )

        var __totalPossibleWorkingDays =

            COUNTROWS (

                CALCULATETABLE(

                 'Working Dates',

                 'Working Dates'[IsWorkday] = "Yes",

             KEEPFILTERS( 'Working Dates'[DateAsInteger] >= __userStartDate ),

             KEEPFILTERS( 'Working Dates'[DateAsInteger] <= __userEndDate )

                )

            )

        var __PossibleWorkingHours =

            __totalPossibleWorkingDays * __numberOfWorkingHoursADay

        var __totalMandatoryWorkedHours =

         CALCULATE (

         SUM ( 'ActivityUpdates'[RegularHours] ),

         'Working Dates'[IsWorkday] = "Yes"

         )

        var __totalOvertimeWorkedHours =

         CALCULATE (

         SUM ( 'ActivityUpdates'[OTHours] ),

         'Working Dates'[IsWorkday] = "No"

         )

        var __totalWorkedHours =

            __totalMandatoryWorkedHours + __totalOvertimeWorkedHours

        var __totalPossibleWorkingHours =

            __PossibleWorkingHours + __totalOvertimeWorkedHours

        return

            divide(

                __totalWorkedHours,

                __totalPossibleWorkingHours

            )

    )

RETURN

    __utilization

[wkeicher]

Hey Derek,

have been struggling with this. Any additional input woudl be greatly appreciated.

Thanks,

Wayne

[Anonymous · Accepted solution]

OK, but what do you expect to see in the third column for each row? Would you please put the figures in there? It would be good if you could share a sample file and tell me what the measure should return for the visual(s) you've got in there. It's really hard to see where the formula breaks if there's no expectation set.

I need to see the data model, mate. If you have any cross-filtering, it might screw up the calculations. I just wonder why 'Working Dates' has a column DateAsInteger. You should not compare an int to a date (which __userStartDate and __userEndDate are). This will not return an error because of an automatic conversion since Date is stored as float and an int can be turned into a float as well.

Thanks.

Best

Darek

[wkeicher]

Darek,

Thanks for this - Looking at th erelationships, I think I fixed it. you are correct in that DateAsIntger (poorly named) was not the correct date field in the Working Dates Table. 

Thanks so much for all your help.

[Anonymous]

OK. Just please mark the answer as THE answer.

Thanks.

Best

Darek

[Anonymous]

Now it's clear. I understand. Before, I did not understand what you wanted to do. If you had added the cases before, I'd have written the correct formula then and there. Give me some time and I'll update the code to take this into account. I'm at work now...

Best

Darek

[wkeicher]

Awesome - Thanks...!