DAX Utilization % - Measure Formula

0
votes

We are trying to perform a Utilization % calculation in DAX and I cannot figure out the forumula. Here's the setup:

Table images are here: http://imgh.us/dax.png

Table 1: There is a [timesheet] Table for our resources (R1, R2, R3, R4). Each timesheet record has a date and number of hours charged.

Table 2: There is a [resource] table with resource [hire date] and [termination date].

Table 3: There is a [calendar] table with available hours for each date. Weekdays have 8 h/day, weekends have 0 h/day

For any given filter context we need to calculate:

Utilization % = (hours charged) / (hours available)

This is not so hard, except for the fact that the (hours available) must only include dates between the [hire date] and [termination date] for each employee. Getting this to work at the aggregate level has been very difficult for us since the calculation must consider each individual employees date range.

The closest we have gotten is:

[hours available] := SUMX(DISTINCT(timesheet[resource_key]), SUM(calendar[utility_hours]))

[hours charged] := SUM(timesheet[bill_hours])

[Utilization %] := [hours charged] / [hours available]

However, this does not perform the resource hire/term date range filtering that is required.

Thanks for any help you can offer!

1
powerpivotdaxpowerbiutilization

1 Answers

1
votes

Your measure for [hours available] needs to be revised so that instead of summing all the utility hours in the calendar, it only sums over a filtered set of rows where the calendar date falls between the start date and the termination date of the respective resource.

[hours available]:=SUMX(DISTINCT(timesheet[resource_key]),CALCULATE(SUM(calendar[utility_hours]),FILTER('calendar',calendar[date_key]>=FIRSTDATE(resources[hire_date_key])),FILTER('calendar',calendar[date_key]<=LASTDATE(resources[termination_date_key]))))

You may want to amend the ">=" and "<=" depending on whether you wish to include the start and finish dates in the calculation.

EDIT: Revised version to pick up where resources are not used in the month, but are 'active'

[hours available]:=SUMX(resources,CALCULATE(SUM(calendar[utility_hours]),FILTER('calendar',calendar[date_key]>=FIRSTDATE(resources[hire_date_key])),FILTER('calendar',calendar[date_key]<=LASTDATE(resources[termination_date_key]))))

But you also need to change your [hours charged] to give zeroes, rather than blanks by adding a zero:

[hours charged]:=SUM(timesheet[bill_hours])+0