cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Adham
Helper III
Helper III

Create 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 IdTask IdProject IdTask Due DateHours
11131-Jan-208
22124-Jan-2032
33117-Jan-2016
34117-Jan-208
35117-Jan-208
36217-Jan-208
37217-Jan-208
18206-Dec-198
39229-Nov-1940
310322-Nov-1916
111314-Feb-20 24
412314-Feb-2032

 

Table 2:

 

Employee IdEmployee Name
1Adham
2Sara
3Kiera
4John

 

Table 3:

 

Project IDProject NameProject Start DateProject End Date
1Engineering Rig10-Jan-202020-Feb-2020
2Build 2d model04-Dec-201930-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.

1 ACCEPTED 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.

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

11 REPLIES 11
Greg_Deckler
Super User
Super User

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 ? 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.