Forum Discussion
Need help with allocating hours
Resident RockstarYour whole 2nd table and its relationships seem odd. What your data SEEMS to be saying is that on day 1 of any simple project, a person should spend 3 hours of work on it; on day 2 of any simple project, a person should spend 7 hours on it; and so on. Right now it (sort of) works because you only have 1 Project of each Complexity.
It's my guess that you need to use Project Name instead of Complexity in your 2nd table. Then I can see that on day 1 of Project A, 3 hours were spent on it.
The next difficulty comes in figuring out data by month. The first step is to set up a date dimension, you can learn about that here. You have a start date and a day counter, so to get a count of hours spent in each month, you would need to set up a measure like:
HoursInMonth = CALCULATE( SUM('Table2'[Hours]), FILTER('Table2', MONTH(DATEADD('Table1'[Start Date], 'Table2'[Day], DAY)) = SELECTEDVALUE(dimDate[Month]) && YEAR(DATEADD('Table1'[Start Date], 'Table2'[Day], DAY)) = SELECTEDVALUE(dimDate[Year])) )This takes the sum of hours where the month part of the date matches the Start date plus that row's day number. Set up your matrix with people as the rows and months/years (from your date dimension, gotta specify both year and month) as columns, and the new [HoursInMonth] measure as the field.
Hi,
You've been really helpful--I think I'm on the right path now with your guidance. I will post my solution if I am able to do it.
Your assumption is correct about Table 2. However, to clarify, Table 1 is just a sample of the data I'm working with; there's actually a bunch of unique project names with the same complexity (being simple, standard, and complex). The point of this is to see the amount of hours that each individual/owner take and also be able to project the estimated hours into the future with current and planned projects. This will later be turned into a capacity sort of dashboard.
Thank you so much for your help and support!