cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yamayancha
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

yamayancha_1-1631517254162.png

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
v-xulin-mstf
Community Support
Community Support

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
Super User
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!




Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

2022 Monthly Feature Releases

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