cancel
Showing results for
Did you mean:
Frequent Visitor

Calculate work day per employee and day

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.

2 REPLIES 2
Community Support

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.

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,

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

Super User

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

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Announcements

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors