cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jeff_Sikich Frequent Visitor
Frequent Visitor

Calculating Dynamic Hours Denominator for Utilization

I've looked through various posts and do not think I have come across a solid answer. I am trying to manipulate DAX to produce a hours denominator that utilizes time intelligence. If I am evaluating 2018, I'd like to produce hours available to work for employees, but I want hire and term dates factored in.  I have a 'all_date' table with a relationship to my 'WIP hours' table by date worked. I also have a 'employee master' table with a relationship to the 'WIP Hours' table by employee ID.  If someone was in the firm for the whole year, I'd like 2088 hrs to be the denominator. However, if someone was hired just between Feb-April, then I'd need a fraction of 2088 hrs. I have a switch function switching day numbers to hour values. 1 & 7 = 0 and the rest = 8.  This column is [StdHrs] within 'all_dates' table. Here is the formula I've been trying to work with:

 

Hours Denominator = SUMX('employee master',CALCULATE(SUM('all_dates'[stdhrs]),FILTER('all_dates','all_dates'[date]>=FIRSTDATE('Employee Master'[DateHire])),FILTER('all_dates','all_dates'[date]<=LASTDATE('Employee Master'[dateterm] ))))
 
I do not think this is producing the intended effect as 2000% utilization is way out there. I was previously calculating the number by producing headcount and then multiplying by stdhrs. Problem with this is headcount using Count functions does not produce fractions for those in the firm a fraction of the year. 
 
The utilization will be sliced by date, pillar, team, etc. 
 
Any help is greatly appreciated. If you need more information, let me know. 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Sikich Frequent Visitor
Frequent Visitor

Re: Calculating Dynamic Hours Denominator for Utilization

@darlove 

 

As I was trying to figure out how t oshare the file, I answered my question qorking with a small smaple of data. I will share my equation for others to reference

 

Standard Hours Denominator incorporating start and term dates for utilization:

Sum of Standard Hours =
SUMX(
'Employee_Master',
CALCULATE(
SUM('All_Dates'[StdHrs]),
FILTER(
'All_Dates',
'All_Dates'[Date]>=FIRSTDATE('Employee_Master'[Hire Date]) &&
'All_Dates'[Date]<='Employee_Master'[Calculated Term Date]
)
)
)

 

Where:

 

[StdHrs] = 0 for Saturday/Sunday and 8 for weekdays regardless of holiday via simple switch statement

 

'Employee_Master'[Calculated Term Date]

IF(
ISBLANK(
'Employee_Master'[Termination Date]),
DATE(9999,12,31),
'Employee_Master'[Termination Date])
 
Above equation inserts a max date for blank term dates. I found when working with a smaller sample that rows with no term date were not being evaluated. So a date of 12/31/9999 should for until after I die. This figure factors in start and term dates when determining the amount of working days available for employee and multiplying it by 8 working hours. 

 

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Calculating Dynamic Hours Denominator for Utilization

Please give a link to a file with the model and some exemplary data (via Google Drive, OneDrive or Dropbox). Please create a simple report that will show what you want to achieve. I'd like to see input and expected output.

 

Thanks.

 

Best

Darek

Jeff_Sikich Frequent Visitor
Frequent Visitor

Re: Calculating Dynamic Hours Denominator for Utilization

I appreciate your help Darek! I will put together a model with samples of the data as to eliminate the refreshing of the SQL databases. I've got some other things going on as well, but will get back to you ASAP. I can share it via OneDrive when ready.  I'm not sure I will get to it today. 

Super User
Super User

Re: Calculating Dynamic Hours Denominator for Utilization

OK. No hurry. Whatever your model storage type is, dual, directquery or native, you can turn it into native with a subset of static data that I need in order to create the right measure for you.

 

Best

Darek

Jeff_Sikich Frequent Visitor
Frequent Visitor

Re: Calculating Dynamic Hours Denominator for Utilization

Hello Darek,

Back again, apologies for the delay. I feel like I am super close. I am having a hard time getting DAX to evaluate employees with blank term dates. Other employees evaluate fine. I tried creating a calculated column saying if term date blank then date 9/9/9999, but now my equation throws a date format error. Here is a link to a sample I created in order to try and work through the problem in a smaller scale making it easier to know what my targets are. 

 

https://sikich-my.sharepoint.com/:x:/p/jeff_fitzgerald/EdY4Vj9wNEVBjJLJB82nxmwB_rwSAbqhDyqmpQs-urQqD...

 

Relevant information should be in here. I have added the necessary tables for now in to the PowerPivot model. The Tables tab will have a table with expected output and measures I am working towards. 

 

The pivot tab has the pivot evaulating the data and some screenshots of equation attempts. 

 

Please let me know if you need anything else. 

Jeff_Sikich Frequent Visitor
Frequent Visitor

Re: Calculating Dynamic Hours Denominator for Utilization

@darlove  I'm pretty sure our organization OneDrive settings do not allow us to share outside of organization. Hmmm, let me think how to share the file. 

Jeff_Sikich Frequent Visitor
Frequent Visitor

Re: Calculating Dynamic Hours Denominator for Utilization

@darlove 

 

As I was trying to figure out how t oshare the file, I answered my question qorking with a small smaple of data. I will share my equation for others to reference

 

Standard Hours Denominator incorporating start and term dates for utilization:

Sum of Standard Hours =
SUMX(
'Employee_Master',
CALCULATE(
SUM('All_Dates'[StdHrs]),
FILTER(
'All_Dates',
'All_Dates'[Date]>=FIRSTDATE('Employee_Master'[Hire Date]) &&
'All_Dates'[Date]<='Employee_Master'[Calculated Term Date]
)
)
)

 

Where:

 

[StdHrs] = 0 for Saturday/Sunday and 8 for weekdays regardless of holiday via simple switch statement

 

'Employee_Master'[Calculated Term Date]

IF(
ISBLANK(
'Employee_Master'[Termination Date]),
DATE(9999,12,31),
'Employee_Master'[Termination Date])
 
Above equation inserts a max date for blank term dates. I found when working with a smaller sample that rows with no term date were not being evaluated. So a date of 12/31/9999 should for until after I die. This figure factors in start and term dates when determining the amount of working days available for employee and multiplying it by 8 working hours. 

 

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 287 members 3,429 guests
Please welcome our newest community members: