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

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.