topic Re: How to calculate working hours per day per employee in DAX Commands and Tips

How to calculate working hours per day per employee

OCBB_SFAFPandA — Mon, 20 Jun 2022 21:51:46 GMT

Hello,

I have data that includes

Date	Emp Id	Hrs
6/20/22	12345	3
6/20/22	12345	8

I need to agregate the data to sum up Emp ID hours for the same day, and flag anything over 8 hrs as OT. Like so

Date	Emp ID	Total Hrs	Reg hrs	OT
6/20/22	12345	11	8	3

This would ideally aggregate to a higher level that does not show employee ID's and shows by locations.

Any help or resources that will help?

Re: How to calculate working hours per day per employee

amitchandak — Tue, 21 Jun 2022 03:39:12 GMT

OCBB_SFAFPandA , Create two measures like

Std hours =

sumx(summarize(Table, Table[Date], Table[Emp ID], "_1", Sum(Table[Hrs]) ), if([_1]> 8,8, [_1]) )

Overtime =

sumx(summarize(Table, Table[Date], Table[Emp ID], "_1", Sum(Table[Hrs]) ), if([_1]> 8, [_1]-8,0) )

Total hours = Sum(Table[Hrs])

Re: How to calculate working hours per day per employee

OCBB_SFAFPandA — Tue, 21 Jun 2022 16:09:56 GMT

Thanks amitchandak !

This worked perfectly! the _1 is the var name you gave the summary total?

if I need a formula to mark any hours worked over 40 hours as OT for the week, how would I do that without dbl counting hours for OT?

You've helped me im previous threads and you are very knowledgeable. I followed your channel and will learn from your videos.

Re: How to calculate working hours per day per employee

jorge_olivares — Wed, 22 Jun 2022 03:15:51 GMT

Hey OCBB_SFAFPandA !

I saw you already got a reply from amitchandak using measures.

I am attaching a table and the dax code for the calculated table. Hope it helps!

Table Name = HoursWorkedTab

Date - EmpID - HoursWorked - OTHours

Friday, June 17, 2022	12	5	0
Friday, June 17, 2022	5	3	0
Friday, June 17, 2022	12	0.55	0
Friday, June 17, 2022	5	3	0
Friday, June 17, 2022	8	9	1
Friday, June 17, 2022	12	6	0
Monday, June 20, 2022	12	0.25	0
Monday, June 20, 2022	12	1	0
Monday, June 20, 2022	5	4	0
Monday, June 20, 2022	8	6	0
Monday, June 20, 2022	8	1	0
Monday, June 20, 2022	8	5	0
Tuesday, June 21, 2022	8	7.5	0
Tuesday, June 21, 2022	12	5	0
Tuesday, June 21, 2022	5	7	0
Tuesday, June 21, 2022	12	1	0
Tuesday, June 21, 2022	5	1	0
Tuesday, June 21, 2022	5	0.3	0
Tuesday, June 21, 2022	12	4	0

SummarizedHours =

SUMMARIZE(

HoursWorkedTab,

HoursWorkedTab[Date],

HoursWorkedTab[EmpID],

"HoursWorked",

SUM(HoursWorkedTab[HoursWorked]),

"RegularHours",

SWITCH(TRUE,

SUM(HoursWorkedTab[HoursWorked]) <= 8, SUM(HoursWorkedTab[HoursWorked]),

SUM(HoursWorkedTab[HoursWorked]) > 8, 8),

"OTHours",

SWITCH(TRUE,

SUM(HoursWorkedTab[HoursWorked]) <= 8, 0,

SUM(HoursWorkedTab[HoursWorked]) > 8, SUM(HoursWorkedTab[HoursWorked]) - 8))

Re: How to calculate working hours per day per employee

OCBB_SFAFPandA — Thu, 23 Jun 2022 16:09:53 GMT

Thanks for the help jorge_olivares!