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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.