0

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!

rocketfish
  • 19
  • 1
  • 3

1 Answers1

1

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
Gordon K
  • 824
  • 1
  • 8
  • 19
  • This is getting closer! See the image of results, [here](http://imgh.us/dax2.png). Per the formula above, resources R1 and R3 have utilization on month 201503, however they are not being included in [hours available]. Btw, here is my formula for Utilization %. `[Utilization %]:=IF(ISBLANK([hours available]) || [hours available]=0, BLANK(), [hours billed]/[hours available])`. Is there a way to refine this so that resources not included in `DISTINCT(timesheet[resource_key])` are still included in [hours available]? – rocketfish Jul 15 '15 at 13:00
  • There is an easier way to handle your utilization %. If you use [Utilization %]:=DIVIDE([hours billed],[hours available]) then this does a smart divide, taking account of Blanks and Zeroes in [hours available] for you. I'll look at the other question now. – Gordon K Jul 15 '15 at 13:13