Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
preolsen
Regular Visitor

Calculate hours worked for employees based on start and end dates

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?

1 REPLY 1
preolsen
Regular Visitor

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

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.