Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION

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

 

gantt.png

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

23 REPLIES 23
tiffanyt123
Helper I
Helper I

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.

 

powerbi.PNG

 

 

I've attached the file as well!!! will truly be grateful for any help :(( @mahoneypat @Fowmy 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hello! 

https://docs.google.com/spreadsheets/d/1QMWuOqvB74ON2ByeOwpLRpQ1FomZkN4e/edit?usp=sharing&ouid=11656...

 

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 :((

 

Fowmy
Super User
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"
)

 

 




Fowmy_0-1595099621930.png

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Fowmy
Super User
Super User

@Anonymous 

Have you tried the Gantt Chart by MAQ: They have got many properties to play with.

Fowmy_0-1595079833224.png

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@Fowmy  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

mahoneypat
Employee
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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

PFB the sample table data,

 

SiteBatch NumberPPT StartPT StartT StartGo LiveNumber of EmpTrainer NameLead Name
A127-Jun-2011-Jul-201-Aug-208-Aug-2013ABCAAA
A24-Jul-2018-Jul-208-Aug-2015-Aug-2015ABCBBB
B311-Jul-2025-Jul-2015-Aug-2022-Aug-2018XYZCCC
B418-Jul-201-Aug-2022-Aug-2029-Aug-2016PQRTBD
B525-Jul-208-Aug-2029-Aug-205-Sep-2020XYZTBD
C61-Aug-2015-Aug-205-Sep-2012-Sep-2011PQRTBD
C78-Aug-2022-Aug-2012-Sep-2019-Sep-2016XYZTBD

 

and the desired output that i am looking for,

 

image.png

 

Hope this helps

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

http://www.informationmagician.com/gantt-like-chart-in-powerbi-using-conditional-formatting-in-the-m...

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

@mahoneypat  can you please guide me on the measure to check if the project is active between certain dates based on the activities

Is it the Site field that you are using for "Stage"?  Which field is used to have a change in color?  Also, which field is used to have a change in darkness of color?  That part may would be trickier.

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

@mahoneypat For change in color I am using site and for change in darkness I am using activity. Basically just want to use Site as a legend field, and can be ignored if we are anyways using a matrix for our solution

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

 

gantt.png

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Please open access to the pbix link cause currently the link cannot be accessed, thanks😊

Hi,

 

Sorry for hijacking the thread.. Im new to power BI and have similar use case and trying to implement the solution you gave.

 

I have value "NA" in for dates, but i need to keep this to show there is no plan decided yet for this particular stage.

Im having issue while trying to create the date table because of this.. How do I convert NA as blank field instead ?

 

Thank you for your help in advance :).

Anonymous
Not applicable

@mahoneypat  This is cool stuff. This would work for me. Just a couple of more questions, is there any way we could highlight the current week / Today as we usually have in the Gantt Charts. And i see that when a phase changes in the matrix there seems to appear a white line, can we show it in a bit more continuous kind of pattern in the matrix visual?

I don't think we could show a today line in the visual.  The most probably would be to override the color of the cells in the current week.  You would then not be able to see the phase.

 

Another idea would be to make a relative week column in the Date table and use that instead of the actual date.  That way, it would show as Week -2, Week -1, Week 0, Week 1, Week 2 across the top and week 0 would be the current week.

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

@mahoneypat  can you please provide access to the PBIX file. I think this would just work for me

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.