Forum Discussion

b2wise's avatar
b2wise
Helper III
5 years ago

DAX Help-Calculating Employee Working Hours

Hi All,

 

I'm new to DAX and am trying to calculate the alltime sum of hours an employee was working. I would then use that number and divide it by the amount of 'days worked' to get an average per day for an employee scorecard report.

 

My data is in a table called 'Workfile' that contains all the individual 'Picks' per employee along with the date and time. It looks something like this:

JOHN Monday, February 22, 2021 11:12:14 AM
SANDY Friday, February 26, 2021 10:34:33 AM
SANDY Friday, February 26, 2021 3:32:09 PM
TRIS Sunday, February 7, 2021 12:33:11 PM
JOHN Wednesday, February 24, 2021 3:46:41 PM
SANDY Thursday, February 18, 2021 4:59:08 PM
JOHN Monday, February 1, 2021 1:15:48 PM
TRIS Thursday, February 4, 2021 10:03:36 PM
TRIS Sunday, February 7, 2021 11:43:27 AM

 

I was thinking to subtract the MIN 'Pick_Time' from MAX 'Pick_Time' to get the hours worked, but I have no idea how to do this over this table which contains thousands of rows with hundreds of rows per date per employee. I do have a date and time table in my dataset.


To summarize: How do I get a single number of the total hours worked by all employees out of a table that has thousands of rows per date with hundreds of different 'Pick_Time' values for each employee.

 

Any help would be appreciated and also if you can explain your thought process in approaching this question.

 

Thank you in advance!

 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    How will u calculate sum of hours worked if you do not know the time of session finished? not clear for me 

    • b2wise's avatar
      b2wise
      Helper III

      By subtracting MIN pick time from MAX pick time, I was thinking to GROUPBY user_pick and pick_date just not sure how to write it.