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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors