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?
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!
I appreciate your help. I have one last question for you:
I have my date table, and I have a table that users will be entering holiday dates on. I need to find a way to have the date table have a holiday column, which shows 1 if that date is a holiday, and 0 if that day is not, based on the holiday table users will enter dates in.
For example, if 1/30 is a holiday at our company, they would enter it in the holiday table. The date table in BI would then show a 1 in the holiday column on the 1/30 date.
I have played around with a few ways to do this but have not got it working how I want. What is the best way to have a custom column that looks at another table like this?
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