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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BisonMike
Regular Visitor

Calculating filled working hours

Hi community

 

I have a problem that is bugging me. I'm trying to how much logging hours our employees has entered in the system compared to the total nr of working hours for the month. If we have 20 workingdays = 160 working hours.

 

Sometime the employee hasnt entered any logged hours. That can be just because they havent logged anything, or that they wasnt employeed at the time.

 

Do you have any ideas how I should calculate this?

 

I have the tables like this:

 

Standard calander with the date marked as a workday or not. For the example i assume all months have 20 workingdays. So for every employee employed there should be 160 working hours that month.

CalenderDateWorkingday
 ... 
 ... 

 

Employment date for when the employee has started working

 

Employment_dateEmployeeIDFromdateTodate
 12017-11-012020-01-01
 22017-12-012020-01-01
 32018-01-012020-01-01

 

 

The table where the employees do the logging:

 

Logged hoursEmployeeIDMonthLogged hours
 1Nov 17120
 1Dec 17140
 1Jan 17140
 2Dec 17100
 2Jan 17100
 3Jan 17100

 

Expected result:

 

OutputEmployeeIDLogged hoursTotal potential working hours
 1400480
 2200320
 3100160
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

You may refer to my solution in this file.  I have assumed working days as Monday - Friday and 8 working hours per day.  Hours will be summed up from the first date in the Calendar table till the Month/Year chosen in the slicer.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

 

You may refer to my solution in this file.  I have assumed working days as Monday - Friday and 8 working hours per day.  Hours will be summed up from the first date in the Calendar table till the Month/Year chosen in the slicer.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you for your quick reply! I have learned lots from that code

 

However I get an annoying error as you see below. The total add up correctly, but if I drill down on employee i get the total of the table for all employees for "hours worked" (and thus effecting hours worked until selection period). Do you know why this is happening? Potential hours are adding upp correctly per employee

 

temp.PNG

Hi,

 

I do not face any such problem.  Download the revised file from here.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you! Works great, I probably mixed something up in my own file

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.