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

 

image

 

This opens up the Query Editor; click on the Add Column tab, and select Custom Column

image

 

Rename the custom column to be Elapsed Duration, and enter the formula = [ProjectFinishDate] – [ProjectStartDate] in the UI

 

image

 

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.

image

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.

 

image

 

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)

 

image

Enjoy,  Ben.