Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Working Hours based on hire / term dates

I'm trying to create a measure to show 8 hours per day (Mon-Fri only), per employee, only if the date is a date the employee was active, based on hire and termination dates.  It seems basic but struggling with this.

 

Below represents 8 hours per day, not considering hire or term dates.  This was calculated using:

Working Hours = sum('Calendar'[Working Hours])*count(Employees[Employee #])

 

 

I'm working with a simple mock data set - a basic employee table and basic calendar table.

 

I do not currently have any relationships between the calendar and employee table.

 

 

 

If I use employee 3, as an example, they should only show 8 hours per day on or after 4/15/2023 and on or before 9/30/2023.

 

What relationship is neccesary to make this work, and what would the measure look like?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    I got it working using:

    = 
 sumx(
    Employees,
    calculate(
        sum('Calendar'[Working Hours]),
        FILTER(
            'Calendar',
            'Calendar'[Date]>=Employees[Hire Date]&&
        'Calendar'[Date] <= if(ISBLANK(Employees[Term Date]),date(9999,12,31),Employees[Term Date]))))

     

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Here some steps that I want to share, you can check them if they suitable for your requirement.

    Here is my test data:

    Create a measure

     

    Active Working Hours = 
CALCULATE(
    SUM('Calendar'[Working Hours]),
    FILTER(
        'Calendar',
        'Calendar'[Date] >= SELECTEDVALUE(Employees[Hire Date]) && 'Calendar'[Date] <= 
        CALCULATE(
            IF(
                SELECTEDVALUE(Employees[Term Date]) = BLANK(),
                DATE(9999,12,31),
                SELECTEDVALUE(Employees[Term Date])
            )
    )
))

     

     

    Final output

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      This gets really close and captures the correct values at the individual person level.  However, the total is not aggregating correctly (see image below).  For example, I would expect the Q1-2023 total to be 3,120.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        I got it working using:

        = 
 sumx(
    Employees,
    calculate(
        sum('Calendar'[Working Hours]),
        FILTER(
            'Calendar',
            'Calendar'[Date]>=Employees[Hire Date]&&
        'Calendar'[Date] <= if(ISBLANK(Employees[Term Date]),date(9999,12,31),Employees[Term Date]))))