Here is my data model
Scenario.xlsx (1.1 MB)
What is the business case problem?
In Tktable I have the Reg Hrs for each employee. However, in EmployeeeAvailableHours there is information about the employees daily available hours.
What am I trying to achieve?
I want a way to get each employee hours in the EmployeeAvailableHours table for each date that is in FYCalendar date table and for each employee in the Employee table in the Tktable so I can compare the reghrs to the available hours and calculate utilisation %
Instead of each employee available hours per day I want to aggregate all of the hours per month and transfer only the monthly figures to Tktable. So for each day in the Tktable were we have reg hours alongside it I want the total employeeavailablehours for the month.
I hope this makes sense. If it is not clear please let me know.
I have attached the case study file below and I appreciate all your efforts for helping me in advance.