Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have some troubles calculating the capacity of working hours per employee in the future but also in the past. The case is:
One employee has a schedule from 01.01.2021 until 31.03.2021 with a average of 32 hours per week. The same employee has a successive schedule from 01-04-2021 with a average of 40 hours per week. The end date is still unknown, but could be be filled in the future.
My table is:
EmployeeID | EmploymentID | StartDate | EndDate | AverageHoursWeek |
500 | 900 | 01.01.2021 | 31.01.2021 | 32 |
500 | 901 | 01.04.2021 | 40 |
And off course I have a dimdate table connected.
What I want to report is that p.e. at 04.01.2021 the employee works 6,4 hours (32 hours divided by 5 working days). The same for p.e. 02.04.2021 but then 8 hours. Example:
04.01.2021 | 6,4 |
05.01.2021 | 6,4 |
... | |
01.04.2021 | 8 |
02.04.2021 | 8 |
et cetera | 8 |
So I always need to report the average working hours with a reference date. Please give me a direction to come to the right calculation. In advance many thnx!
Solved! Go to Solution.
Use COALESCE to substitute potential blank values with the maximum date you want to use, for example 2021-12-31.
First step is to define what you mean by week. Are all employees in the same time zone? Same country? Saturday half days?
Then you use GENERATESERIES to compute the daily work time as you describe, and then you create the measure for the cumulative total.
Hi @lbendlin , all employees ar working in the same timezone and same country. And yes, I would like to except the weekend days, but that is the finishing touch 🙂
GENERATESERIES is not allowed with blank values, in my case the end date is sometimes blank. To be clear: an employee works 40 hours every week. How do I get this in a matrix for p.e. the year 2021, depending on if the employee still has an active employment? Can you help me further on? Many thnx!
Use COALESCE to substitute potential blank values with the maximum date you want to use, for example 2021-12-31.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |