Forum Discussion
Overtime Report Help
Hi,
I need some help designing the best way to make an overtime report work.
Right now, I have a table that has every labor entry employees have made. The important columns are:
Employee number, date, and hours (among some others).
I need to find a way to show overtime hours (which for this question lets just say is anything over 40 hours in a week, other tweaks will need to be made but I will figure out that problem as I go).
What is the best way to sum hours by week and find when an employee id who may have 20 entries that need to be summed in any given week, is over the 40 hour threshhold?
I can give further criteria and information as needed, just need a hand figuring out the best logic to handle what I need.
Thanks!
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
31 Replies
AppleMan ,
First thing I would do is add a Calendar or Date Table if you have not already done so. This is a PBI best practice and numerous videos are available to be had.
Once you have a proper Date Table, you will then need to define when your PayWeek starts. Do you use Sun to Sat, Mon-Sun, or some other combination. Here is a small data sample.Join this Date Table to your Fact Table (Labor entries) via the Date Column. You will then be able to Sum your Hours column by your defined PayWeek. Then create a measure or calculated column to determine OT.
Overtime = SumofHours - 40
Hope this gets you started. I will continue to try to assist if you hit any hurdles along the way.Regards,
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,
