Calculating a measure with a filter imposed by a table relationship

[scondon1985]

I have a project where employees enter time (timesheets table). Some employees are missing timesheets.

I am trying to determine the Available amount of time each employee can work. This is based on summing the workdays between the employee start date and termination date, and multiply by 8 hours per day. This is the Available calculation:

Avail =

var __StartDT = max('Employee'[STARTDT])

var __TermDT = max('Employee'[TERMDT])

var __Amount =

 CALCULATE (

    sumx('Date', if([IsWeekDay]=TRUE, if([Date] >= __StartDT, If ([Date] <= __TermDT,  if ([Date] <= TODAY(), 8, 0), 0), 0) ))

)

RETURN __Amount

Test 1 - Inactive relationship: This works great if I deactivate the relationship between the Date and Timesheets table.

Test 2: Active relationship: But when I activate the relationship between Dates and Timesheets, only Dates with matching timesheet records are counted in the available measure.

I read something about crossfiltering the tables to nullify the relationship. This is what the Available calculation looks like after adding the crossfilter:

Avail =

var __StartDT = max('Employee'[STARTDT])

var __TermDT = max('Employee'[TERMDT])

var __Amount =

 CALCULATE (

    sumx('Date', if([IsWeekDay]=TRUE, if([Date] >= __StartDT, If ([Date] <= __TermDT,  if ([Date] <= TODAY(), 8, 0), 0), 0) ))

    ,

    CROSSFILTER('Date'[Date] , 'Timesheets'[WORK_DATE], None)

)

RETURN __Amount

This produces an odd result which I will demonstrate with Empl Steve:
Test 1 for empl Steve (inactive relationship): This is correct. Steve has been employeed the whole duration of the Date table:

Test 2 for empl Steve (active relationship):

Test 3 for empl Steve (using CrossJoin): Note that it shows the whole of FY2023 as available, even though I have only one timesheet that year.

 When I remove the date column, it shows the whole duration of the calendar as available:

So the question is, how to compute Available hours correctly without regard for how many timesheets the employee has entered.

[scondon1985]

By "Correct", I mean that the available hours should equal 8 hours times the number of work days the employee was employed during the time period in question. So if an employee was employed the entire year or 2023, their available hours would be 2080 for 2023, If they were only employed the last workday of 2023, their available hours would be 8, regardless of whether or how many timesheets they entered.

See second screenprint in the original post for the correct and expected result (I am getting an error trying to paste it here)

This is a revised description omitting some info that may be unnecessary:

I have a project where employees enter time (timesheets table). Some employees are missing timesheets.  (see ERD in original post)

I am trying to determine the Available amount of time each employee can work during a given time period. This is based on summing the workdays between the employee start date and termination date, and multiply by 8 hours per day. This is the Available calculation:

Avail =

var __StartDT = max('Employee'[STARTDT])

var __TermDT = max('Employee'[TERMDT])

var __Amount =

 CALCULATE (

    sumx('Date', if([IsWeekDay]=TRUE, if([Date] >= __StartDT, If ([Date] <= __TermDT,  if ([Date] <= TODAY(), 8, 0), 0), 0) ))

)

RETURN __Amount

Test 1 - Inactive relationship: This works as expected if I deactivate the relationship between the Date and Timesheets table.

Test 2: Active relationship: But when I activate the relationship between Dates and Timesheets, only Dates with matching timesheet records are counted in the available measure.

My question is, how to compute Available hours independent of how many timesheets the employee has entered.

[lbendlin]

Define "correctly".

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...