Employee Utilization Rate - Incorrect Roll-up to month level when calculating Expected Hours

[MEverts]

Dear all,
I’m calculating employee utilization rates as the proportion of billable hours (actuals), divided by the Expected Hours (projected) minus Leave-hours (actuals):

[Billable_Hrs]/([Expected_Hrs] - Leave_Hrs) – between selected dates.

I need to ‘project/calculate’ the expected number of hours ([Sum_Expected_hrs] below) for multiple employees across different date-granularities (e.g. per department for a particular month), however, my current measure fails to roll-up the Expected Hrs correctly to a month- or year-level when selecting multiple-employees with overlapping Start- and Stop-dates. This makes any Utilization-% calculations incorrect for months where employees left the company.
I have:

• A date-dim table ‘Calendar’ (with work-days)
• An Employee-dim/fact table 'Direct Reports_Last_Active', which lists unique employee information, with their [start-date] & [stop-date], as well as their contracted hours per day [Hrs_per_day]. (Stop-date=Today() when currently employed).
• A time-sheet-fact table ‘Timesheet_NB’ with actuals for Billable/Non-billable & Leave hours for all employees.

I try to summarize the situation in the table below:

Here is the syntax for my failing measure Sum_Expected_hrs:

Sum_Expected_hrs =
VAR CurrentStart =
    MIN ( 'Direct Reports_Last_Active'[Min_Start] )
VAR CurrentEnd =
    MAX ( 'Direct Reports_Last_Active'[Stop_date] )
VAR _CurrDate =
    CALCULATE (
        MAX ( 'Calendar'[date] ),
        FILTER ( 'Calendar', 'Calendar'[Date] >= CurrentStart ),
        FILTER ( 'Calendar', 'Calendar'[Date] <= CurrentEnd ),
        FILTER ( 'Calendar', 'Calendar'[WEEKDAY] = 1 )
    )
VAR _Count_Expected_Hrs =
    CALCULATE (
        SUM ( 'Direct Reports_Last_Active'[Hrs_per_day] ),
        FILTER (
            'Direct Reports_Last_Active',
            'Direct Reports_Last_Active'[Min_Start] <= _CurrDate
                && 'Direct Reports_Last_Active'[Stop_date] >= _CurrDate
        ) //      ,'Direct Reports_Last_Active'[Employee_ID]=xxxxx  // Filter to create [Empl_A] / [Empl_B]
    )
VAR Days_in_selection =
    CALCULATE (
        COUNTROWS ( 'Calendar' ),
        FILTER ( 'Calendar', 'Calendar'[Date] >= CurrentStart ),
        FILTER ( 'Calendar', 'Calendar'[Date] <= CurrentEnd ),
        FILTER ( 'Calendar', 'Calendar'[WEEKDAY] = 1 )
    )
VAR Sum_Expected_Hrs =
    CALCULATE (
        Days_in_selection * _Count_Expected_Hrs,
        FILTER ( 'Calendar', MIN ( 'Calendar'[Date] ) < 'Calendar'[Date] ),
        FILTER (
            'Calendar',
            'Calendar'[Date] >= MIN ( Timesheet_NB[Date] )
                && 'Calendar'[Date] <= MAX ( Timesheet_NB[Date] )
        ),
        USERELATIONSHIP ( 'Calendar'[Date], 'Timesheet_NB'[Date] ),
        USERELATIONSHIP ( 'Direct Reports_Last_Active'[Employee_ID], Timesheet_NB[Employee] )
    )
RETURN
    Sum_Expected_Hrs

Earlier though, I located this very elegant measure which calculates the cumulative total of expected hours (CSum_Expected_hrs_total). This measure works really well, and I understand why, but I haven’t been able to adjust it to pull the regular sum of Expected Hours, as i'm trying to do in the above measure. I include it as it may prove a better approach anyway:

CSum_Expected_hrs_total =
VAR CurrentDate =
    MAX ( Calendar[Date] )
VAR HoursPerDay =
    ADDCOLUMNS (
        'Direct Reports_Last_Active',
        "Hrs_day",
            CALCULATE (
                SUMX ( 'Direct Reports_Last_Active', 'Direct Reports_Last_Active'[Hrs_per_day] ),
                USERELATIONSHIP ( 'Direct Reports_Last_Active'[Employee_ID], Timesheet_NB[Employee] )
            )
    )
VAR Result =
    CALCULATE (
        SUMX (
            HoursPerDay,
            VAR CurrentStart = 'Direct Reports_Last_Active'[Start_DateFrame]
            VAR CurrentEnd = 'Direct Reports_Last_Active'[Stop_date]
            VAR DaysGone =
                IF (
                    CurrentDate >= CurrentStart,
                    IF (
                        CurrentDate > CurrentEnd,
                        NETWORKDAYS ( CurrentStart, CurrentEnd, 1 ),
                        NETWORKDAYS ( CurrentStart, CurrentDate, 1 )
                    ),
                    0
                )
            RETURN
                [Hrs_day] * DaysGone
        ),
        USERELATIONSHIP ( 'Direct Reports_Last_Active'[Employee_ID], Timesheet_NB[Employee] )
    )
RETURN
    Result

Thank you so much!

Martijn

[MEverts · Accepted solution]

Dear all,
I found a solution in the following thread:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Include-Employee-start-and-end-date-in-Utiliz...

Best regards, Martijn

[MEverts]

Dear Yadong, Thank you so much for your reply.
Unfortunately I haven't been able to get the desired result with the use of the ALL() function.
I did make a demo-pbix file, modeled after my actual data with copied measures and tables:
https://www.dropbox.com/s/q4asl41bklzzsib/Expected_Hrs_September.pbix?dl=0
If you, or anyone else, would like to take a look, that would be much appreciated!

Best regards,

Martijn

[v-yadongf-msft]

Hi @MEverts ,

Please use ALL( ) function.

ALL( ) returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

For more information, please refer to:ALL function (DAX) - DAX | Microsoft Learn

Best regards,

Yadong Fang

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