Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jeff_Sikich
Regular 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

@Anonymous 

 

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
Anonymous
Not applicable

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

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. 

Anonymous
Not applicable

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

@Anonymous 

 

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. 

 

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. 

@Anonymous  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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors