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

     

6 Replies

  • 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