The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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?
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))
User | Count |
---|---|
80 | |
74 | |
41 | |
30 | |
28 |
User | Count |
---|---|
108 | |
96 | |
53 | |
48 | |
47 |