Need help with allocating hours

[Anonymous]

Hi Power BI Community,

I'm new to Power BI desktop and have been learning a lot online (using YouTube and this forum), but recently, I've hit a road block.

I'm trying to create a matrix that returns the number of hours with the two axis (employee name [rows] and month [columns]). I've been having trouble creating a measure that returns the projected running total of hours per month.

I have two tables:

The first table is the list of all the projects with the owner of each project, the start and end date, and the complexity.

Project Name	Owner	Start Date	End Date	Complexity
Project A	Chad	12/13/2018	1/2/2019	Simple
Project B	Jennifer	11/29/2018	2/6/2019	Standard
Project C	Jennifer	6/2/2018	12/1/2018	Complex

The second table is the day numbers (Day 1, 2, 3...) with the respective hours the employee would spend each day for one project (based on complexity).

Day	Complexity	Hours
1	Simple	3
2	Simple	7
3	Simple	1
4	Simple	1
5	Simple	1
6	Simple	1
7	Simple	1
8	Simple	1
9	Simple	1
10	Simple	1
11	Simple	1
12	Simple	1
...	...	...

Over 1K rows...

Below is my desired outcome:

	June	July	August	September	October	November	December	January	February
Chad	0	0	0	0	0	0	45	16	0
Jennifer	100	110	150	147	110	100	143	0	0

My goal is to have a matrix that has the total allocation of hours per month based on Table 2. Correct me if I'm wrong (because I'm new to Power BI) but the relations between the two table should be 'Table 1'[Complexity]*:*'Table 2'[Complexity].

Please let me know if you need further clarification. I really appreciate your support! Thank you in advance.

[Cmcmahan]

Your 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.

[Anonymous]

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!

[Greg_Deckler]

Can you post your data as text? And I don't understand how those tables relate to one another, can you post expected output? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi,
That link helps a lot! I really appreciate it. I've edit the post to the desire format. Please let me know if you have any other questions or concerns.