Calculate work day per employee and day

[yamayancha]

Dear Power BI community,

I've been struggling with this one measure for a while now and I feel like it should be way easier than it appears to me, so I am turning to your wisdom for help because I can't seem to get it to work.

What it should do

This company has a bunch of employees with different number of hours to work per week. Those can change during the year. I need a measure that calculates the total number of hours all employees should/could have worked over time.

Example

There are 4 employees. Two of which work at a factor of 1 which means 1*7.7 hours per day (, means . in this example, the database is in German), one also works at a factor of 1 but only started working there in April

One employee usually works at a factor of 0.6 which means 0.6*7.7 (4.62) hours per day - but during the month of July they worked full-time.

I have a calendar table which includes if a day is a work day or not ('Calendar'[work day] = true) so I can easily calculate the sum of hours an employee is supposed to work during the year. My issue is the changing of the factor over time. In the end there are a bunch of measures using the amount of hours calculated here, so if this is not calculated correctly, nothing else will be.

[v-xulin-mstf]

Hi @yamayancha

Maybe you can use the calendar table to calculate the total number of working days contained in different periods of time for different factor over time.

According to your example table, you can expand each date range and the merge to calendar table.

About how to expand the continuous date interval.

Please refer my blog:

https://community.powerbi.com/t5/Community-Blog/Expand-the-continuous-date-interval-Excluding-weeken...

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[vanessafvg]

What is the issue that you have with the factor over time?  can you provide your calculations?  You would probably need to iterate using a sumx or an iterator over the factor to get the accurate factor at that time, but without seeing your calculations its very hard to know.   Also when you post sample data its best to do that in  text