Forum Discussion
Overtime Report Help
AppleMan ,
Various ways this can be done. I have attached an updated pbix that shows one of these ways.
Import your Holiday Table. Connect it to your Date Table. It should be 1-1 relationship.
Then I created a Calculated Column in your Date Table using SWITCH and LOOKUPVALUE.
I structured it to give you 0's and 1's, but you can replace these with other values if you so choose ("Holiday" or "Work Day").
Let me know if you run into any issues....
Regards and Good Luck
I indeed do have a date table, always use one. Did not think of adding a column for the first day of the week, very smart.
This is where things get a little tricky. For employees working Mon-Fri, the threshhold for overtime is 40 hours. Simple enough.
However, for employees working Sat/Sun, or specifically noted by a shift code I have in my table of hours, their threshhold is anything worked over 8 hours in a day is overtime.
So my thoughts were it would be easiest to find the overtime of the normal week (Mon-Fri), then the overtime of the weekend shift workers, and add the two together as a final step to get the total weeks overtime (Sun-Sat). I planned to do this in a measure ran by variables.
I think I can do this by summing the pay hours by week, mon-fri, then summing pay hours for that same period where the shift code is the weekend code. Then add the two together. Does that sound logical? Any flaws or concerns as I size up this report and its capabilities?
AppleMan ,
Believe your logic will work.
Personally, I would start by creating another Calculated Column called something like "Weekend OT" so I can clearly see if the Total Measures are working properly. Once you are confident this is working accurately, then create the Measures as you have described.
Best Regards,
Are you saying you would make a column that is basically running the measure and totalling the "Weekend OT" over time to see if it is including and excluding what it should be?
AppleMan ,
Apologies for any confusion. Here is a sample of what I had in mind.
Weekday OT_Total = 40 - SumofHours. (This is 2 in my sample below)WeekendOT (calc column) = [Hours] - 8
WeekendOT_Total = SUM( Table[WeekendOT] ) = 6Name Date WeekStart Hours Weekend OT John 5-Feb 5-Feb 8.00 John 6-Feb 5-Feb 9.00 John 7-Feb 5-Feb 9.00 John 8-Feb 5-Feb 8.00 John 9-Feb 5-Feb 8.00 Jane 10-Feb 5-Feb 10.00 2 Jane 11-Feb 5-Feb 12.00 4 I'm thinking a couple of steps ahead, as it will probably be useful to your Users (Mgmt) to differentiate between Weekday and Weekend OT (just based on my experience).
Hope this helps...and again, did not mean to confuse the situation.
Regards,
I just looked at my data and unfortunately it wont be this easy. There can be multiple hour records on the same day for these weekend shifts. So before I take the 8 hours off of them they need to be summed.
For example:
Name Date WeekStart Hours Weekend OT Jane 10-Feb 5-Feb 3.69 Jane 10-Feb 5-Feb 4.41 Jane 10-Feb 5-Feb 2.51 It coud look like this. I would think at this point I need to do some kind of summarized table inside a measure that summs each entry together for each day first. Thoughts?