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

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

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'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. 

    • Jeff_Sikich's avatar
      Jeff_Sikich
      Regular Visitor
      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.