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

California Overtime Calculation!!

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

 

2 REPLIES 2
Greg_Deckler
Super User
Super User

@roopammundada How is your duration stored? Is it a decimal number or the number of seconds or number of minutes or ?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  I have edited the post with sample data and eamples

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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