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 %
Twist?
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.
@BrianJ@AntrikshSharma this might be around your alley to knock this apart. Thanks in advance
Your file didn’t include a mock up with desired results, so I’m not sure I interpreted it correctly…
Added a boolean filter column to the EmployeeAvailableHours table
Hello Patrick,
here are my two cents provided that I understood correctly your requirements: “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 %”
I have to admit that a bit more data in the “Scenario.xlsx” would help in order to better check up the flow. However, this is what I did.
First I removed all the tables in the provided pbix except the FYCalendar and the supporting query, then extracted the tables in your spreadsheet. (renamed the tables adding “-Scenario” in the end)
I merged Tktable with EmployeeAvailableHours so that I could get the Hours per day in the table mainly for being able to create relationships between tables. This is how it looks now:
HI @Melissa thanks for this solution but when I tried to apply it to my model it just was too big. I failed to mention that the employeeavailablehours table has about 200k rows.
So just for completeness I have managed to anonymise the data and I have attached it here to give you the full picture of what my data looks like and what I am trying to achieve.
My end result
I want to be able to add on the TKtable a column with available hours (This comes from the employeeavailablehours table) and another column annual leave hours (this comes from the Annualeavehrs table).
Here is an screenshot of an example of the end result for employee 375
So for Employee 375 his total available hours for the month of july (only working days) is 176 and the amount of annual hours (in the current month July) he has is zero (because although he submitted them we only accept hours approved from description column of the annualleavehrs table).
I hope this is much clearer and I have include a sample PBIX file to work on.
Hi @ambepat, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!
Hi @ambepat, we’ve noticed that no response has been received from you since July 23. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
