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 ,
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?
AppleMan ,
Please review attache pbix file.
Have created a dummy fact table premised on your example.
Created a series of Measures. I am sure these can be written in a more succint manner, but I wanted to be clear in my methodology. You can modify as you wish.
Please review tomorrow and if there is anything you are unclear about, please let me know.Have a great evening!