cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
roopammundada
New Member

Overtime Calculation Power BI Dax

Hi There, Can someone help me with here - I'm struggling hard from last 2 days to convert my input to output desired format.

Here is my input table:

Employee NameLocationCheck-inCheck-outTotal HoursUnpaid breakPayable HoursStatusOvertime
Person AWillits, CA02/26/2023 06:23 PM02/27/2023 12:05 PM14:1400:0008:00Weekend, Present14:14
Person AWillits, CA02/27/2023 06:55 PM02/28/2023 09:12 AM11:1200:0008:00Present03:12
Person AWillits, CA02/28/2023 06:44 PM03-01-2023 05:0209:0500:0008:00Present01:05
Person AWillits, CA03-01-2023 19:2603-02-2023 06:0606:4600:0006:46Present 
Person AWillits, CA03-02-2023 19:0003-03-2023 06:2808:5400:0008:00Present00:54
Person AWillits, CA03-03-2023 20:0003-04-2023 06:1508:3500:0008:00Present00:35
Person AWillits, CA03-04-2023 21:2703-04-2023 23:5702:3000:0008:00Weekend, Present02:30
Person AWillits, CA03-05-2023 18:4103-06-2023 06:0906:4600:0008:00Weekend, Present06:46
Person AWillits, CA03-06-2023 19:0503-07-2023 05:5009:3100:0008:00Present01:31
Person AWillits, CA03-07-2023 18:3603-08-2023 05:4809:5100:0008:00Present01:51
Person AWillits, CA03-08-2023 18:3803-09-2023 05:0209:3400:0008:00Present01:34
Person AWillits, CA03-09-2023 18:4803-10-2023 10:2912:4000:0008:00Present04:40
Person AWillits, CA03-10-2023 19:0203-11-2023 10:5511:5000:0008:00Present03:50
Person AWillits, CA03-11-2023 19:0003-12-2023 11:0009:4100:0008:00Weekend, Present09:41
Person AWillits, CA03-05-2023 18:4103-06-2023 06:0906:4600:0008:00Weekend, Present06:46
Person AWillits, CA03-06-2023 19:0503-07-2023 05:5009:3100:0008:00Present01:31
Person AWillits, CA03-07-2023 18:3603-08-2023 05:4809:5100:0008:00Present01:51
Person AWillits, CA03-08-2023 18:3803-09-2023 05:0209:3400:0008:00Present01:34
Person AWillits, CA03-09-2023 18:4803-10-2023 10:2912:4000:0008:00Present04:40
Person AWillits, CA03-10-2023 19:0203-11-2023 10:5511:5000:0008:00Present03:50
Person AWillits, CA03-11-2023 19:0003-12-2023 11:0009:4100:0008:00Weekend, Present09:41

 

 

 

I'm looking for the below output table. Where total time in the input table is categorized into Payable Hours and Overtime. What i'm looking for is to convert the overtime from above table into two categories 1.5x and 2.0 based on these logics.

1) 1.5x hourly rate for work over 8 hours in a workday and 40 hours in a workweek
2) 1.5x hourly rate for the first 8 hours on the seventh consecutive day of work
3) 2x hourly rate for work over 12 hours in a workday
4) 2x hourly rate for work over 8 hours on the seventh consecutive day of work

Based on the above logics you see that the below table has the 1.5x and 2.0x columns satisfying the all the conditions.
Final Output

First NameClock In DateClock In TimeClock Out DateClock Out TimeTimezoneTotal HoursDeductionsStandard HoursOvertime 1.5x HoursOvertime 2.0x Hours
Person A26-02-202304:53:0026-02-202322:35:00PDT14.250842.25
Person A27-02-202305:25:0027-02-202319:42:00PDT11.2083.20
Person A28-02-202305:14:0028-02-202315:32:00PDT9.1081.10
Person A01-03-202305:55:0001-03-202316:37:00PDT6.806.800
Person A02-03-202305:30:0002-03-202317:01:00PDT90810
Person A03-03-202306:30:0003-03-202316:47:00PDT8.6301.27.430
Person A04-03-202307:57:0004-03-202310:28:00PDT2.52002.520
Person A05-03-202305:11:0005-03-202316:40:00PDT6.7806.7800
Person A06-03-202305:35:0006-03-202316:26:00PDT9.63081.630
Person A07-03-202305:05:0007-03-202316:23:00PDT9.99081.990
Person A08-03-202305:08:0008-03-202315:35:00PDT9.63081.630
Person A09-03-202305:18:0009-03-202321:01:00PDT12.750840.75
Person A10-03-202305:32:0010-03-202321:26:00PDT11.8501.2210.630
Person A11-03-202305:30:0011-03-202321:30:00PDT9.680081.68
0 REPLIES 0

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors