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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors