By Nate Auchter, MCSE MCT
Back in 2016 Microsoft released a Gantt visual for Power BI as seen in the post here. The visual is a fantastic starting point for those that require this type of visual in their reports. However, if you intend on using this visual with Project Online data, it is important to note that the duration value for the visual expects a calendar duration, and this doesn’t directly align to the way that Microsoft Project and Project Web App (PWA) store duration values.
Duration values in Microsoft Project and PWA are stored by default in hours. In addition, these values exclude the calendar exceptions (read “Holidays”) that may fall within the project timeline (within the start and finish dates).
In order to force the visual to provide a more accurate duration for the projects or tasks displayed within it, you must create a new calculated column in your data model to provide the calendar date difference (in days) between the start date and the finish date (whether it’s an assignment, task, or project). The out of the box duration fields include calendar exceptions, and Power BI doesn’t automatically know that, or which days to add back in.
So, to solve this, we just need to add a calculated column to the specific table we care about (in our example we’ll use the ‘Projects’ table) in the data model. To do this, follow the steps below:
- Open the Power BI report you’re using that contains the data from Project Online that you want to visualize with the Gantt visual.
- From the ‘Report’ view, expand the table in the ‘Fields’ pane that contains the data you need to visualize with a “Duration”. In our case, we want the ‘Projects’ to be displayed as bars in the Gantt, so we will expand the Projects table, then right click the ‘Projects’ heading and select ‘New Column’.
- In the formula bar type the following (if you’re using the Projects table):
Calendar Duration = DATEDIFF(Projects[Project Start Date],Projects[Project Finish Date],DAY)
Now, you also might have an issue, where there may be projects in your system that somehow have a start date that is equal to the finish date and Power BI may not like this formula. If that’s the case, you cans solve the problem by building a little error handling into the formula. We need to set the value to “0” if there is an error (meaning if the start date is equal to the finish date). That formula looks like this:
Calendar Duration = IFERROR(DATEDIFF(Projects[Project Start Date],Projects[Project Finish Date],DAY),0)
- Once you’ve entered the formula, press enter on your keyboard, et voila, you have your calendar duration to use as the duration in the Gantt visual.
- You probably already know how to add a field to a visual by now, but if not, simply drag your new column over to the ‘Duration’ entry box in the visual configuration.
And now, if your Gantt visual originally looked something like this:
It might now look something like this, accounting for the non-working days that were originally exluded:
About Sensei Project Solutions
Sensei Project Solutions, a Finalist for the 2015 Microsoft Project and Portfolio Management (PPM) Partner of the Year, focuses on bringing Instant Productivity to your team. Our mission is to help individuals and organizations be more productive so that they can achieve their greatest potential. As a Gold certified Microsoft Partner and Registered Education Provider (R.E.P.) with the Project Management Institute (PMI®), Sensei offers a complete set of services and products for a successful Microsoft PPM deployment. Our guiding principles for Proactive PPM follow best practices and industry standards aligned with the Project Management Institute (PMI) and Gartner, enabling organizations to manage resource demand, obtain business intelligence that facilitates better decision making, increase business effectiveness by easily connecting people, and become self-sufficient with PPM processes and solutions. In short, Sensei helps organizations achieve Instant Productivity.
About Nate Auchter, MCSE MCT