08 Dec Gantt Charts in Power BI part II #ProjectOnline #PowerBI #GanttChart
It came to my attention recently that my previous post on Gantt Charts in Power BI needed slight modification in order to display the correct length of the Gantt bar. In the post I used the ProjectDuration to display the duration on the Gantt bar, however this is held in “working hours” and not “elapsed days” (which the visualisation expects) and so we need to use Power Query to create a new calculation.
Edit the Query to create a new column
In PowerBI Desktop, click on the Edit Queries button
This opens up the Query Editor; click on the Add Column tab, and select Custom Column
Rename the custom column to be Elapsed Duration, and enter the formula = [ProjectFinishDate] – [ProjectStartDate] in the UI
Scroll to the right of the table and review the new column. You’ll see the elapsed duration displayed in DD:HH:MM:SS format.
It’s not likely that you’ll get a whole number in the Elapsed duration column, because (by default) tasks start @ 08:00 and finish at 17:00. The best thing to do is to Transform the column to days or Change the Type to be a Whole Number – both options will work.
Edit the Gantt Chart Visualisation
Now that we’ve created the Elapsed Duration column we can update the visualisation mappings (in red bold below)
| Field name | Typical Project field | Comment |
| Legend (optional) | ProjectOwnerName or Custom ECF such as sponsor | The legend is a the way to colour projects which have similar attributes, such as the owner or sponsor. |
| Task | ProjectName | Typically we’re using Project level data, but in truth we could display a Gantt chart for task level data. |
| Start Date | ProjectStart | |
| Duration | Elapsed Duration | New calculated column = [ProjectFinishDate] – [ProjectStartDate] |
| % Completion | ProjectPercentCompleted | Displays a bar from the project start date through to the %complete value. By default this is a thin black line, but the line colour can be changed by selecting a different colour within Format | Task completion | Completion Colour. |
| Resource (optional) | ProjectOwnerName | The Resource field is displayed to the right of the Gantt chart, and typically displays the name of the resource assigned to the task. For our Project level gantt chart, I’ll either leave it blank or use the owner field (as in this case) or some other relevant field per project (eg Sponsor, department, customer name etc) |
Enjoy, Ben.