Creating a Gantt Chart with PowerBI | by Abdullah Kurkcu | Nov, 2022
After failing many times to create a Gantt Chart using existing visuals, I decided to build one myself using a matrix visual in PowerBI
There was actually a PowerBI visual that I was already using for creating Gantt charts. However, when I tried to share it, I realized it was impossible without getting a paid subscription. You need a subscription not only for yourself but also everyone else that needs to see your Gantt Chart.
I thought the easiest way to build it was to use a Matrix visual in PowerBI to create one similar to how you create them in Excel, by changing the cell color to make the cells look like schedule progress bars. Then, my needs immensely increased because I wanted to plot a line for today’s date, I had to differentiate between processes and milestones, and so on.
I found many videos online, and they were immensely useful. Here is one of them.
The first thing is to load data to PowerBI. I was working with Primavera schedules. When the Primavera schedule is exported to an Excel file, it automatically divides the levels of processes by using various indentation levels. Indentation does not properly work for PowerBI, so, if you can label each task with some process ID, it works like a charm. Here is a snapshot of my schedule data.
The image below shows the upload screen on PowerBI. Once it is uploaded, use the first row as headers if it is not already done so. Make sure that your Start and Finish dates are in the right format. I also made the start and finish dates the same for milestones so that it won’t color a cell when these two dates are the same. Instead, it will show an icon for a milestone.
Here is the code in the Advanced Editor if you need it.
Once the data is uploaded to your dashboard, create an empty matrix visual. Start adding company name, Sub ID, Activity ID, Activity Name, Start and End into the rows section of the visual.
Once you add all these columns, you will realize that there is now a “+” button next to the company name. If you expand all levels, it will look like the image below.
The first thing you need to do to change this is to turn off the stepped layout option. If you click on the matrix visual and then format visual, you need to find the “Row header” dropdown. There, you will see the “Options” button at the bottom.
The same options page lets you turn off +/- icons as well. Now, your matrix visual should look like the image below.
We can use a filter on this page to not show any row that has “NA”. On the filters pane, select Sub ID and then select all. Unclick NA and that will remove the rows that have NA as the sub-id from your visual.
To create date brackets, we will need a piece of DAX code. Go back to your Model screen on the left menu and let’s generate a calendar for our schedule.
Once your click “New Table”, PowerBI will let you write a DAX code starting with “Table =”. Here is the code we will use to create our calendar. Our dates will start from 01/01/2022 and go all the way to 2026. You don’t have to add quarters, weeks, and/or days if you don’t need them in your schedule.
Calendar =VAR _start = DATE(2022,1,1)VAR _end = DATE(2026,1,1)RETURN ADDCOLUMNS( CALENDAR(_start,_end) ,"Year", YEAR([Date]) ,"Year Month", YEAR([Date]) & " " & FORMAT([Date], "mmmm") ,"Quarter", "Q" & QUARTER([Date]) ,"Month Number", MONTH([Date]) ,"Month", FORMAT([Date], "mmmm") ,"Week", WEEKNUM([Date], 1) ,"Day", DAY([Date]) ,"Date Slicer" , IF([Date] = TODAY(), "Today" , IF( [Date] = TODAY() - 1, "Yesterday" , FORMAT([Date], "dd mmmm yyyy") & "" ) )
)
If your calendar is created, you need to add the date variables to your columns of the matrix visual. I am only going to add Year & Quarter.
The next thing is to add some values to these date columns we created. We need to write a function that looks at the process dates and adds 0 or 1 based on the date range. If the date range (start and end of the project) coincides with the calendar dates we created, the cell should have 1, otherwise 0. To do that, you need to create a new measure in your schedule data.
BetweenDates = VAR beginning = min ( Sheet1[Start] ) VAR finish = max ( Sheet1[Finish] ) VAR duration = max ( Sheet1[Original Duration] ) VAR colorVar = 0RETURNSWITCH (TRUE(),AND ( MAX ( 'Calendar'[Date] ) >= beginning , MIN ( 'Calendar'[Date] ) <= finish ) && duration >0,1
)
We also would like to show a line for Today’s date and use an icon to illustrate milestones. Therefore, the code will first check if the start and end dates are the same for a process. If they are, it will fill that cell with a⭐. Otherwise, it will use “|” in a cell to imitate a line by repeating it in each row. We should create another measure called “TodayLine” for this.
TodayLine = VAR TodayLine = "|" VAR beginning = min ( Sheet1[Start] ) VAR finish = max ( Sheet1[Finish] ) VAR duration = max ( Sheet1[Original Duration] )RETURNSWITCH ( TRUE (), duration == 0 && MAX ( 'Calendar'[Date] ) >= beginning && MIN ( 'Calendar'[Date] ) <= finish , "⭐", ISINSCOPE( 'Sheet1'[Sub ID]), IF ( TODAY () IN DATESBETWEEN ( 'Calendar'[Date], MIN ( 'Calendar'[Date] ), MAX ( 'Calendar'[Date] ) ), TodayLine, ""))
Once you create these two measures, drag TodayLine to the Values of your matrix visual.
The last thing we need to do is to change cell colors in the matrix visual based on the BetweenDates measure that we created.
Once you click on fx, the input screen will require a format style. Select rules and then find your BetweenDates field to base the rule. If the cell is equal to 1, it should have a color.
Now, our schedule is ready, and it should look like the image below!
There is a way to color each of these processes by process type. We need to create another measure to differentiate various tasks by color. If you don’t need it, you can skip the next part.
If it is not a milestone and we want to color it by its process name, the DAX code should be structured as follows:
Color by Process = IF ( [BetweenDates] <> 0 , SWITCH ( MAX ( Sheet1[Sub ID] ), "Conceptual Design", "#14375A","Company Tasks", "#CC7B29"))
If you use that, your schedule will look like this:
In conclusion, we all know that Gantt charts are a well-known and successful way of project management and scheduling. An effective, efficient, and practical method for planning, monitoring, and controlling processes in projects where numerous activities are intended to be accomplished within a specific time range is to create dashboards with embedded Gantt charts.
I hope that creating a dashboard with a Gantt chart will ensure that everything is finished on schedule, let you easily visualize crucial dates, and milestones, and set dependencies for your projects.
If you have any questions, leave a comment.
Dashboard Github repo: https://github.com/ak4728/PowerBI-schedule
Thanks for reading!
After failing many times to create a Gantt Chart using existing visuals, I decided to build one myself using a matrix visual in PowerBI
There was actually a PowerBI visual that I was already using for creating Gantt charts. However, when I tried to share it, I realized it was impossible without getting a paid subscription. You need a subscription not only for yourself but also everyone else that needs to see your Gantt Chart.
I thought the easiest way to build it was to use a Matrix visual in PowerBI to create one similar to how you create them in Excel, by changing the cell color to make the cells look like schedule progress bars. Then, my needs immensely increased because I wanted to plot a line for today’s date, I had to differentiate between processes and milestones, and so on.
I found many videos online, and they were immensely useful. Here is one of them.
The first thing is to load data to PowerBI. I was working with Primavera schedules. When the Primavera schedule is exported to an Excel file, it automatically divides the levels of processes by using various indentation levels. Indentation does not properly work for PowerBI, so, if you can label each task with some process ID, it works like a charm. Here is a snapshot of my schedule data.
The image below shows the upload screen on PowerBI. Once it is uploaded, use the first row as headers if it is not already done so. Make sure that your Start and Finish dates are in the right format. I also made the start and finish dates the same for milestones so that it won’t color a cell when these two dates are the same. Instead, it will show an icon for a milestone.
Here is the code in the Advanced Editor if you need it.
Once the data is uploaded to your dashboard, create an empty matrix visual. Start adding company name, Sub ID, Activity ID, Activity Name, Start and End into the rows section of the visual.
Once you add all these columns, you will realize that there is now a “+” button next to the company name. If you expand all levels, it will look like the image below.
The first thing you need to do to change this is to turn off the stepped layout option. If you click on the matrix visual and then format visual, you need to find the “Row header” dropdown. There, you will see the “Options” button at the bottom.
The same options page lets you turn off +/- icons as well. Now, your matrix visual should look like the image below.
We can use a filter on this page to not show any row that has “NA”. On the filters pane, select Sub ID and then select all. Unclick NA and that will remove the rows that have NA as the sub-id from your visual.
To create date brackets, we will need a piece of DAX code. Go back to your Model screen on the left menu and let’s generate a calendar for our schedule.
Once your click “New Table”, PowerBI will let you write a DAX code starting with “Table =”. Here is the code we will use to create our calendar. Our dates will start from 01/01/2022 and go all the way to 2026. You don’t have to add quarters, weeks, and/or days if you don’t need them in your schedule.
Calendar =VAR _start = DATE(2022,1,1)VAR _end = DATE(2026,1,1)RETURN ADDCOLUMNS( CALENDAR(_start,_end) ,"Year", YEAR([Date]) ,"Year Month", YEAR([Date]) & " " & FORMAT([Date], "mmmm") ,"Quarter", "Q" & QUARTER([Date]) ,"Month Number", MONTH([Date]) ,"Month", FORMAT([Date], "mmmm") ,"Week", WEEKNUM([Date], 1) ,"Day", DAY([Date]) ,"Date Slicer" , IF([Date] = TODAY(), "Today" , IF( [Date] = TODAY() - 1, "Yesterday" , FORMAT([Date], "dd mmmm yyyy") & "" ) )
)
If your calendar is created, you need to add the date variables to your columns of the matrix visual. I am only going to add Year & Quarter.
The next thing is to add some values to these date columns we created. We need to write a function that looks at the process dates and adds 0 or 1 based on the date range. If the date range (start and end of the project) coincides with the calendar dates we created, the cell should have 1, otherwise 0. To do that, you need to create a new measure in your schedule data.
BetweenDates = VAR beginning = min ( Sheet1[Start] ) VAR finish = max ( Sheet1[Finish] ) VAR duration = max ( Sheet1[Original Duration] ) VAR colorVar = 0RETURNSWITCH (TRUE(),AND ( MAX ( 'Calendar'[Date] ) >= beginning , MIN ( 'Calendar'[Date] ) <= finish ) && duration >0,1
)
We also would like to show a line for Today’s date and use an icon to illustrate milestones. Therefore, the code will first check if the start and end dates are the same for a process. If they are, it will fill that cell with a⭐. Otherwise, it will use “|” in a cell to imitate a line by repeating it in each row. We should create another measure called “TodayLine” for this.
TodayLine = VAR TodayLine = "|" VAR beginning = min ( Sheet1[Start] ) VAR finish = max ( Sheet1[Finish] ) VAR duration = max ( Sheet1[Original Duration] )RETURNSWITCH ( TRUE (), duration == 0 && MAX ( 'Calendar'[Date] ) >= beginning && MIN ( 'Calendar'[Date] ) <= finish , "⭐", ISINSCOPE( 'Sheet1'[Sub ID]), IF ( TODAY () IN DATESBETWEEN ( 'Calendar'[Date], MIN ( 'Calendar'[Date] ), MAX ( 'Calendar'[Date] ) ), TodayLine, ""))
Once you create these two measures, drag TodayLine to the Values of your matrix visual.
The last thing we need to do is to change cell colors in the matrix visual based on the BetweenDates measure that we created.
Once you click on fx, the input screen will require a format style. Select rules and then find your BetweenDates field to base the rule. If the cell is equal to 1, it should have a color.
Now, our schedule is ready, and it should look like the image below!
There is a way to color each of these processes by process type. We need to create another measure to differentiate various tasks by color. If you don’t need it, you can skip the next part.
If it is not a milestone and we want to color it by its process name, the DAX code should be structured as follows:
Color by Process = IF ( [BetweenDates] <> 0 , SWITCH ( MAX ( Sheet1[Sub ID] ), "Conceptual Design", "#14375A","Company Tasks", "#CC7B29"))
If you use that, your schedule will look like this:
In conclusion, we all know that Gantt charts are a well-known and successful way of project management and scheduling. An effective, efficient, and practical method for planning, monitoring, and controlling processes in projects where numerous activities are intended to be accomplished within a specific time range is to create dashboards with embedded Gantt charts.
I hope that creating a dashboard with a Gantt chart will ensure that everything is finished on schedule, let you easily visualize crucial dates, and milestones, and set dependencies for your projects.
If you have any questions, leave a comment.
Dashboard Github repo: https://github.com/ak4728/PowerBI-schedule
Thanks for reading!
Denial of responsibility! Techno Blender is an automatic aggregator of the all world’s media. In each content, the hyperlink to the primary source is specified. All trademarks belong to their rightful owners, all materials to their authors. If you are the owner of the content and do not want us to publish your materials, please contact us by email – [email protected]. The content will be deleted within 24 hours.