Forum Discussion
Gantt Chart with different stages
Hi All, I am very new to Power BI and as a part of my project I have been asked to build a Gantt chart. But I have a specific requirement for the same. Apart from the basic start and end dates I have certain other project stages in the data which i do want to have in the Gantt chart. I tried using some custom charts and most of them just provide me an option to enter just the start and end dates. Have also tried using Gantt chart by Lingaro which allows me to put the various dates and also shades it, however it has some limitations with respect to dates and tooltips.
I am not able to figure out a way out of this. How can I achieve this task by using a custom visual or even by playing around my data and using stacked bar chart or a matrix may be. Can someone please help me in this.
I can provide the data format or my exact requirement of needed.
Please see this link for the example pbix file on how to do this. Here is an image of the result. Of course, you can adapt the colors, and hide the text if you want (by using same color measure on Text color in conditional formatting as I used for background).
In the pbix, you will see a simple DAX Date table to get the Week Dates for the columns. Note there is no relationship between the two tables. There are also 3 measures - 1 to check if the project is active during that week, 2 to detect which phase/stage it's in, and 3 return a color based on phase/stage. You can get more granular with color codes (I just used "Green", "Blue", etc. to demonstrate). You can also build in some logic based on the Site value to get different color families.
https://drive.google.com/file/d/1UcKquV6A4oTRmonMDx7PKXtUGtmQwzQZ/view?usp=sharing
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
23 Replies
- Fowmy
Super User
Anonymous
Hi, interesting challenge!
I added a Date Table as well, you can filter the date range you need.Download the file: https://1drv.ms/u/s!AmoScH5srsIYgYF4ae4tNJ-euUK2Vg
Here is the Measure for the Matrix Value:Measure = VAR _PPTD = MAX(Projects[PPT Start]) VAR _PTD = MAX(Projects[PT Start]) VAR _TD = MAX(Projects[T Start]) VAR _PPGOLIVE = MAX(Projects[Go Live]) VAR _DATE = MAX(Dates[Date]) VAR _PPTM = CALCULATE( AND(_DATE >= _PPTD,_DATE <= _PPGOLIVE ) * 1, TREATAS(VALUES(Dates[Date]),(Projects[PPT Start])) ) VAR _PTDM = CALCULATE( AND(_DATE >= _PTD,_DATE <= _PPGOLIVE ) * 1, TREATAS(VALUES(Dates[Date]),(Projects[PT Start])) ) VAR _TDM = CALCULATE( AND(_DATE >= _TD,_DATE <= _PPGOLIVE ) * 1, TREATAS(VALUES(Dates[Date]),(Projects[T Start])) ) VAR _GOLM = CALCULATE( (_DATE = _PPGOLIVE) * 1, TREATAS(VALUES(Dates[Date]),(Projects[Go Live])) ) VAR RESULT = (_PPTM + _PTDM + _TDM + _GOLM) RETURN IF(RESULT=0,BLANK(),RESULT)Conditional Formating Measure (Use for Fill and Font Colors), You can change the colors you need.
CF = SWITCH([Measure], 1,"GREY", 2,"YELLOW", 3,"BLUE", 4, "GREEN", "WHITE" )If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS ? to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query and Excel- Fowmy
Anonymous
Hi, I got my One Drive working now and attached the PBIX file, please download and check with your data and share your feedback.
If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query and Excel Fowmy Thank you for your solution. This too would work in my case and is on lines to what i was looking for. Thank you for your help.
- mahoneypat
Microsoft Employee
It would be helpful to add example data (as a table, not an image) to your post, along with an image of your desired output.
Regards,
Pat
PFB the sample table data,
Site Batch Number PPT Start PT Start T Start Go Live Number of Emp Trainer Name Lead Name A 1 27-Jun-20 11-Jul-20 1-Aug-20 8-Aug-20 13 ABC AAA A 2 4-Jul-20 18-Jul-20 8-Aug-20 15-Aug-20 15 ABC BBB B 3 11-Jul-20 25-Jul-20 15-Aug-20 22-Aug-20 18 XYZ CCC B 4 18-Jul-20 1-Aug-20 22-Aug-20 29-Aug-20 16 PQR TBD B 5 25-Jul-20 8-Aug-20 29-Aug-20 5-Sep-20 20 XYZ TBD C 6 1-Aug-20 15-Aug-20 5-Sep-20 12-Sep-20 11 PQR TBD C 7 8-Aug-20 22-Aug-20 12-Sep-20 19-Sep-20 16 XYZ TBD and the desired output that i am looking for,
Hope this helps
- mahoneypat
I would suggest you use the trick of turning a matrix visual into a Gantt chart. Please see these two links
https://www.youtube.com/watch?v=SO4mk1H94OA
You will need to also create a Date table that has a Week column to get the values for the Columns in your matrix. It also involves a measure that will check if the project is active during that week, so it can be colored to get the Gantt type view.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
- Fowmy
Anonymous
Have you tried the Gantt Chart by MAQ: They have got many properties to play with.If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query and ExcelFowmy Have tried the Gantt chart by MAQ, but it doesn't have an option to enter multiple stages as you can see in my data. It simply has start and end dates and it does not provide any different colors or any indication regarding the different stages in my data
Hi there,
Thought this thread was super useful for my use case!!! However - how do I create a gantt chart matrix that has gaps between different months? Something like this - where if the Date falls within that calendar month, it will be assigned a category and therefore colour coded in the matrix.
I've attached the file as well!!! will truly be grateful for any help :(( mahoneypat Fowmy
- mahoneypat
Not totally sure what you mean. The described approach should work for that too, as the date is maxdate is >= and the mindate is <= to your date. Plus, you should unpivot your data and, since you have single dates, you may be able to simplify things by just making a direct relationship between a Date table and your new unpivoted Date column.
To share your file, you'll need to provide a link to it on a fileshare like Google Drive.
Pat
Hello!
For "date is maxdate is >= and the mindate is <= to your date" --> does this logic determine whether the date falls within that month?
So sorry Im new to powerBI and am not super sure how to do it and create relationships 😕 appreciate your help if possible :((
