This is how you create a Gantt chart with the standard Power BI Matrix
Gantt charts are often used in project management. They allow you to see in a blink of an eye the type of activities and their duration. The activities are displayed as a bar against a horizontal time line.
Every project and every business is different, so too are the requirements that business users have for a Gantt chart. In Power BI there is no default Gantt chart, but the AppSource offers plenty. These custom out-of-the-box visuals have their own additional functionalities, some of which are more elaborate than others. Depending on the business case, you can decide which one will fit your needs.
Our customers’ needs were the following:
- Show the duration of the activities
- Show a hierarchy of projects and related activities
- Show more than 1 milestone on the timeline
- Show the status of past and future milestones with icons
Unfortunately, none of the AppSource Gantt charts met our ostensibly simple requirements. Therefore, we created our own chart with the default Power BI Matrix visual and DAX.
Our business case
Our customer had a specific idea in mind. Each of their projects have multiple activities and every activity has a set of due dates for writing specific reports. These due dates are also called assessment dates or milestones. They wanted to see in a blink of an eye the current status of these milestones in order to take action where needed.
Each project has multiple activities and each activity has 1 or more delivery dates, also known as milestones. This could be, for example, a delivery date for an assessment, a check or a report. We started with the following questions to get to the requirements:
Customer: “Can we show the activities on a timeline, by start and end date, grouped by its parent project?”
BI Consultant: “Yes, that is no problem, most Gantt visuals have this basic hierarchy functionality.”
Customer: “Our activities can have multiple assessment due dates and the number of assessments can vary per activity. Can we add these milestones on the time line for each activity?”
BI Consultant: “Some Gantt Charts offer the possibility to add at least 1 milestone, but it varies per visual. If you want to show more than 1 milestone it becomes tricky.”
Customer: “If we show one or more milestones, can we also show what the current status is of that particular milestone?”
BI Consultant: “This is really particular. I do not think it is possible to format each milestone based on specific information with any of the out-of-the-box visuals. Let me investigate and figure out a solution.”
Gantt Chart Comparison
Of the Gantt charts available in the AppSource, only two can show milestones and hierarchies: Gantt 2.2.3 and Gantt Chart xViz. But these visuals only partly meet our customers’ needs.
| Name | Milestone 1 field* | Milestone 1+ fields** | Hierarchy | Paid license |
| Acterys Gantt v1.1.1 | x | |||
| ADWISE Roadmap v2.0.3 | x | x | ||
| Craydec Timelines | x | |||
| Gantt 2.2.3 | x | x | ||
| Gantt Chart – xViz | x | x | x | |
| Gantt chart by Lingaro | x | |||
| Gantt chart by MAQ Software | x |
*1 field: this means you can only select 1 column from your dataset for the milestone value
**1+ fields: this means you can select more than 1 columns from your dataset for the milestone value field
Gantt 2.2.3 is one of the charts we often use. It is simple in design and meets most basic requirements. However, you can add only one milestone column field in this visual. If you want to show multiple milestones (with a data model such as ours – see part 2), it will create additional rows, which we do not want. It also lacks additional formatting options for each specific milestone.
Gantt Chart xViz was our best option, as we can show hierarchies and add more than 1 milestone field. Unfortunately, our activities can have multiple milestones and the number of milestones can change per activity. So, the number of necessary column fields will differ per activity. Making it impossible to manage in the data model. Additionally, if you use and share the free version in the Online Service it shows a banner blocking your view. A paid license to remove the banner and unlock all paid functionalities will cost a considerable amount based on the number of people who will be reading the report.
Even though the xViz visual is rich in its applications, for our business case we will only use a fraction of its total capabilities. The investment is simply not worth it.
Pricing – xViz – Advanced Visuals for Power BI
A custom made Gantt chart
At Dynamic People we go the extra mile to offer the best solution for our customers. The best option was to create a Gantt chart with the Power BI default matrix visual and DAX. By thinking creatively we were able to meet our customers’ requirements.
In the Gantt chart we want to see at a glance: which milestones are completed, cancelled or pending, and on which milestone to focus first: the next assessment due date.
By using conditional formatting with icons, we can achieve this: a green checkmark for completed, a red cross for cancelled, a yellow flag for pending and an exclamation mark for the next assessment due date.
The desired result is achieved by creating a numeric code for each activity line and date. The numeric code denotes if the activity was active within that date range and if there was a milestone during that date range. It is important to focus on the date range, as we can move up and down in the timeline hierarchy, from year to specific date.
Each milestone has a status field with one of four values: completed, cancelled, pending report or in progress. With DAX we created a measure that encoded all these values with a number.
Due to the complex measure to compute all the numeric values in the chart, the loading time of our custom visual is longer than the out-of-the-box visuals from the AppSource. By adding single-select filters on the page loading time is minimized. However, it is something our customer has to consider while making the final choice.
The result
Our Gantt chart met all customer requirements, and they chose our custom solution. Any of the out-of-the-box visuals would mean a trade-off between the requirements, which was more important than the trade-off in loading time. Happy customer, happy consultant 🙂
More info?
Do you have specific wishes for you Gantt Chart? Let us know, we love to think along.