Calculate Hours Employee should have worked

[Tad]

Hello all,

Your help is appreciated in advance. Sharing dummy data to help.

I am attempting to calculate the hours an employee should have worked given a start date, a termination date or today if they have no termination date. I have 2-3 tables I have to check: 
1 - the Calendar table with all of the dates and a teh weekday count column

2 - the time entry table with all time entries (billable, non-billable, pto, holiday)

3 - the employee status table that has start and term dates - I have done a simple lookup to have this added to each row for the given employee on table #2. so if the solution doesn't need this in a separate table then great.

Dummy Data:
#1 - Calendar:

Date	Weekday?
5/1/2022	0
5/2/2022	1
5/3/2022	1
5/4/2022	1
5/5/2022	1
5/6/2022	1
5/7/2022	0

#2 Combined time Entries

Date	Name	Start Date	Hours Worked	Term Date	Last Day Worked
5/2/2022	John Doe	4/1/2022	8		6/9/2022
5/3/2022	John Doe	4/1/2022	8		6/9/2022
5/1/2022	Jane Doe	4/1/2022	2		6/9/2022
5/2/2022	Jane Doe	4/1/2022	8		6/9/2022
5/3/2022	Jane Doe	4/1/2022	8		6/9/2022
5/4/2022	Jane Doe	4/1/2022	8		6/9/2022
5/5/2022	Jane Doe	4/1/2022	8		6/9/2022
5/6/2022	Jane Doe	4/1/2022	8		6/9/2022
5/7/2022	Jane Doe	4/1/2022	1		6/9/2022
5/1/2022	Joe Doe	4/1/2022	8	5/3/2022	5/3/2022
5/2/2022	Joe Doe	4/1/2022	8	5/3/2022	5/3/2022
5/3/2022	Joe Doe	4/1/2022	8	5/3/2022	5/3/2022

#3 Employee Start and End Date:

Name	Start Date	Term Date
John Doe	4/1/2022
Jane Doe	4/1/2022
Joe Doe	4/1/2022	5/3/2022
Placeholder for max date	1/1/1900	12/31/3000

Currently I am using the following formula to calculate what we call "Base Hours":

Base Hours =

((Calculate(

sum('Calendar'[WEEKDAY?]),

Filter('Calendar', 'Calendar'[Date] >= Min('#3'[Start Date]) && 'Calendar'[Date] <= MAX('#3'[Term Date]))

,Filter('Calendar', 'Calendar'[Date] >= Min('#2'[Date]) && 'Calendar'[Date] <= MAX('#2'[Date]))

))*8)*DISTINCTCOUNT('#2'[Employee Name])

The problem with this is that the second filter in the calculate filters down to the max time entry for the specific employee in the visual meaning it will not could the weekdays for John doe after his last time entry on May 3rd even though he was employed on later days. I need my formula to include the later days if the employee has not been terminated. 

Thanks!

Edit: I know I have to wrap this in a HASONEFILTER after to get the totals to work right in table visuals. I am doing that in a separate measure to keep it clearer for the other person working with me.

[Tad · Accepted solution]

I acheived my desired result by doing a DAX CROSSJOIN between the Calendar and the Combined Time Queries table.

new table = CROSSJOIN(

SUMMARIZECOLUMNS('Combined Time Entries'[Employee Name],'Combined Time Entries'[EMAIL_ADDRESS]),

DISTINCT('Calendar'[Date]))

NOTE: I have to use email address as it essentially the Employee ID between my databases.

I then created calculated columns to check if the employee was employed on the date and another to check it the date was a weekday. I was then able to calculate how many hours the employee should have worked that day.

[v-xiaotang]

Hi @Tad 

Thanks for reaching out to us.

I've created a sample file based on your data, but could you please provide expected result of it? For example, a desired table picture and calculation formula(Although you provide the measure code, I can't tell if some logic is correct, so it would be better to provide the calculation formula directly), and then I will convert the formula to measure

Best Regards,

Community Support Team _Tang

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

[Tad]

I figured it out using a crossjoin. I needed a time entry for each employee for each day because the employees are not good about entering time and constantly miss a day here or there and I was having a hard time working up a formula that took that into account. So I fabricated date+emplooyee entries by crossjoining my dynamic calendar table with the employee names/ID (email addresses). I should remark: I appreciate the welcome. This is actually my second account on the forum as I switched jobs and lost access to my prior organization account. Previously I was one step shy of Super User. I don't know why this one threw me for a loop other than I have previously avoided doing DAX cross joins as I am more comfortable doing them in SQL.