Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.