Forum Discussion

Jeff_Sikich's avatar
Jeff_Sikich
Regular Visitor
7 years ago
Solved

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 ...
  • Jeff_Sikich's avatar
    Jeff_Sikich
    6 years ago

    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. 

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Jeff_Sikich
Regular Visitor
6 years ago

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-urQqDA?e=j5wyKt

 

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's avatar
    Jeff_Sikich
    Regular Visitor
    6 years ago

    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.