Working Hours based on hire / term dates

[Jon_S]

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?

[v-heq-msft]

Hi @Jon_S ,

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

[Jon_S]

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.

[Jon_S · Accepted solution]

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]))))