Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am trying to create a bar chart (for project management) with weeks on my axis and number of hours spent working each week as my values. The aim of this is to see if a certain person is working more than 40 hours a week or not. Here is a small extract of my full dataset.
Table 1:
Employee Id | Task Id | Project Id | Task Due Date | Hours |
1 | 1 | 1 | 31-Jan-20 | 8 |
2 | 2 | 1 | 24-Jan-20 | 32 |
3 | 3 | 1 | 17-Jan-20 | 16 |
3 | 4 | 1 | 17-Jan-20 | 8 |
3 | 5 | 1 | 17-Jan-20 | 8 |
3 | 6 | 2 | 17-Jan-20 | 8 |
3 | 7 | 2 | 17-Jan-20 | 8 |
1 | 8 | 2 | 06-Dec-19 | 8 |
3 | 9 | 2 | 29-Nov-19 | 40 |
3 | 10 | 3 | 22-Nov-19 | 16 |
1 | 11 | 3 | 14-Feb-20 | 24 |
4 | 12 | 3 | 14-Feb-20 | 32 |
Table 2:
Employee Id | Employee Name |
1 | Adham |
2 | Sara |
3 | Kiera |
4 | John |
Table 3:
Project ID | Project Name | Project Start Date | Project End Date |
1 | Engineering Rig | 10-Jan-2020 | 20-Feb-2020 |
2 | Build 2d model | 04-Dec-2019 | 30-Jan-2020 |
3 | Deployment of Rig | 15-Nov-2020 | 30-Feb-2020 |
I feel that there are many steps needed to achieve my goal which i am incapable of doing:
- I need a method of getting a start date of the task then estimating number of hours spent each WORKDAY working on the task. I was thinking of using my task due date and working backwards by allocating 8 hours for each WORK DAY until i reach a calculated start date.
- I then need to add up all of these hours spent on each workday and allocating them to work-weeks to be able to plot my graph
- I will need to add a line on the graph for 40 hours a week to see if the employee or group of employees (thier average) are working more or less than 40 hours a week
- I will also need to be able to filter the graph by employee and project
I have been looking around for 2 days and trying different methods but i was left with little luck. I know there are many steps involved but i would be really grateful if someone can help me out.
Solved! Go to Solution.
OK @Adham , I want to make the comment that this took quite a bit of thought and work. But, I believe I have what you are looking for finally. See attached PBIX file. Had to create the estimated Start Date and then create a table like this:
Employee Weeks =
SELECTCOLUMNS(
ADDCOLUMNS(
FILTER(
ADDCOLUMNS(
GENERATE(
DISTINCT('Table'[Employee Id]),
CALENDAR(MIN('Table'[Task Start Date]),MAX('Table'[Task Due Date]))
),
"__IsWeekDay",IF(WEEKDAY([Date],3) < 5,TRUE(),FALSE())
),
[__IsWeekDay] = TRUE()
),
"__Work Hours",8
),
"Employee Id",[Employee Id],
"Date",[Date],
"Work Hours",[__Work Hours]
)
In this new table, I created these columns:
Week = WEEKNUM([Date])
Total Work Hours =
VAR __Table =
ADDCOLUMNS(
FILTER(
'Table',
'Table'[Employee Id] = 'Employee Weeks'[Employee Id]
),
"Must Work",IF([Date] >= [Task Start Date] && [Date] <= [Task Due Date],1,BLANK())
)
VAR __TotalHours = SUMX(__Table,[Must Work]) * [Work Hours]
RETURN
IF(ISBLANK(__TotalHours),BLANK(),__TotalHours)
I created the bar chart using a categorical Y Axis to remove blank values. Two different bar visuals, one tied to a slicer and one not tied to a slicer.
OK, so to start off with, what are you thinking around start date of a task? Are we going with a single start date for the project or ?
Hello @Greg_Deckler !
Thank you very much for the fast response and your help so far. I actually just edited my question to include the start and end date of the project. However, i think this will not be of use to produce this graph as the task due dates are what we require.
When it come to the start date of the task, it is just a method of estimating as i mentioned. We will probably have to manually enter the start dates as well. But for now, i was think of using the due date of a task, and working my way backwards to reach a certain start date. So for example, if we have a task that is 32 hours and the end date of it is on 4-March-2020, the start date would be 28-Feb-2020. Weekends will not be included as ofcouse people only work on workdays. (Well, that is what is says on paper atleast :D)
That is my best method so far Please do let me know if you have got a better one!
OK, this will be a fun one. I have a NET WORK DAYS function in the Quick Measure Gallery, so will have to use some form of that. It's going to take some time to work through it.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362
Thank you very much @Greg_Deckler ! Really do appreciate your help! Looking forward to seeing what you come up with!
So, are you looking for something like the attached?
Hello Greg,
Thank you very much for that. Something a little more in detail would be perfect. So what you did is that you calculated the number of hours a person would work in the week that the due date would fall on. Here is an example of what i am after:
you have got 2 tasks:
Task 1
- Hours: 32
- Employee Id: 1
DueDate: 26/2/20
Task 2
- Hours :40
- Employee Id: 1
DueDate: 24/2/20
For task one the employee will have to work on:
- 20/2/20 - 8 Hours
- 21/2/20 - 8 Hours
- 24/2/20 - 8 Hours
- 25/2/20 - 8 Hours
For Taks 2 the employee will have to work on:
- 17/2/20 - 8 Hours
- 18/2/20 - 8 Hours
- 19/2/20 - 8 Hours
- 20/2/20 - 8 Hours
- 21/2/20 - 8 Hours
which means that in total, employee id 1 will be over worked on 20/2/20 and 21/2/20.
- 17/2/20 - 8 Hours
- 18/2/20 - 8 Hours
- 19/2/20 - 8 Hours
- 20/2/20 - 16 Hours
- 21/2/20 - 16 Hours
- 24/2/20 - 8 Hours
- 25/2/20 - 8 Hours
This will be seen on the barchart as it will show that on the week of 17/2/20 - 21/2/20, that employee is working a total of 56 hours.
OK @Adham , I want to make the comment that this took quite a bit of thought and work. But, I believe I have what you are looking for finally. See attached PBIX file. Had to create the estimated Start Date and then create a table like this:
Employee Weeks =
SELECTCOLUMNS(
ADDCOLUMNS(
FILTER(
ADDCOLUMNS(
GENERATE(
DISTINCT('Table'[Employee Id]),
CALENDAR(MIN('Table'[Task Start Date]),MAX('Table'[Task Due Date]))
),
"__IsWeekDay",IF(WEEKDAY([Date],3) < 5,TRUE(),FALSE())
),
[__IsWeekDay] = TRUE()
),
"__Work Hours",8
),
"Employee Id",[Employee Id],
"Date",[Date],
"Work Hours",[__Work Hours]
)
In this new table, I created these columns:
Week = WEEKNUM([Date])
Total Work Hours =
VAR __Table =
ADDCOLUMNS(
FILTER(
'Table',
'Table'[Employee Id] = 'Employee Weeks'[Employee Id]
),
"Must Work",IF([Date] >= [Task Start Date] && [Date] <= [Task Due Date],1,BLANK())
)
VAR __TotalHours = SUMX(__Table,[Must Work]) * [Work Hours]
RETURN
IF(ISBLANK(__TotalHours),BLANK(),__TotalHours)
I created the bar chart using a categorical Y Axis to remove blank values. Two different bar visuals, one tied to a slicer and one not tied to a slicer.
Hello @Greg_Deckler ,
I Would really appreciate if you could assist me on something else. It is a continuation of this problem. I have posted it here https://community.powerbi.com/t5/Desktop/Filter-created-table-based-on-multiple-conditions/m-p/95535...
Thank you very much!
Hello Greg,
This is exactly what i was looking for! Thank you very much for this! I really do appreciate your help and effort to assist me!
OK, still working on this. I have come up with the estimated start date.
Task Start Date =
VAR __EstimatedDays = 'Table'[Hours] / 8 + 'Table'[Hours] / 40 * 2
VAR __EstimatedStartDate = ('Table'[Task Due Date] - __EstimatedDays) * 1.
VAR __Table =
ADDCOLUMNS(
FILTER(
ADDCOLUMNS(
CALENDAR(__EstimatedStartDate,'Table'[Task Due Date]),
"__IsWeekDay",IF(WEEKDAY([Date],3) < 5,TRUE(),FALSE())
),
[__IsWeekDay]=TRUE()
),
"__WorkHours",8
)
VAR __Table2 =
ADDCOLUMNS(
__Table,
"__ReverseCumulative",SUMX(FILTER(__Table,[Date] >= EARLIER([Date])),[__WorkHours])
)
RETURN
MAXX(FILTER(__Table2,[__ReverseCumulative] >= 'Table'[Hours]),[Date])
Well, the simple answer is to use Microsoft Project which will figure out all of this for you. That being said, I will take a look because it is interesting and you were kind enough to provide data as text which makes things sooooooo much easier!
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |