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
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] ) = 6
| Name | 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?