Calculate hours worked for employees based on start and end dates

[preolsen]

Hello!

I need to calculate the hours all my employees have worked, given date columns and their employment percentage. 100 % employment is 37.5 hours weekly. Therefor hours = employment_percentage * 0.375

In this dataset we have two employees.

emp_id	__START_AT	__END_AT	employment_percentage	unit_id
1	01.01.2023	15.01.2023	100 %	500
1	15.01.2023		90 %	501
2	01.01.2023	15.01.2023	100 %	500
2	15.01.2023	31.01.2023	50 %	500

Employee 1 worked 15 days for department 500, but is currently working for department 501.

Employee 2 also worked 15 days for department 501, but lowered his employment to 50 % for the remaining month and quit. 

I've worked out this bit, to make sure the employees are active in the selected period in Power BI.

The MISSING CODE needs some kind of DAYSDIFF code, am I right?

amount_hours_worked =
CALCULATE (
    SUM ( table[employment_percentage] ) * 0.375 * [MISSING CODE],
    AND (
        table[__START_AT] <= MAX ( dim_date[Date] ),
        OR ( table[__END_AT] > MIN ( 'dim_date'[Date] ), ISBLANK ( table[__END_AT] )
)))

Could anyone help me solve this one?

[preolsen]

I believe I have it almost solved. I added a dim_date table in the solution.

hours_worked = 
VAR _maxdate = MAX(dim_date[Date])
VAR _start = min('table'[__START_AT])
VAR _end = IF(ISBLANK(SELECTEDVALUE('table'[__END_AT])), _maxdate, max('table'[__END_AT]))
RETURN

CALCULATE (DATEDIFF(_start, _end, DAY) * ( SELECTEDVALUE('table'[employment_percentage]) * 7.5))