Forum Discussion
Helper IIICreate bar chart with weeks on axis using end date and calculated start date
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.
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.
11 Replies
- Greg_Deckler
Community Champion
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!
- Greg_Deckler
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 ?
AdhamHello 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!
- Greg_Deckler
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