Creating Calendar Duration for use with the Gantt Visual in Power BI | Sensei Project Solutions

Sensei Blog & News

The blog about Microsoft productivity, project and portfolio management and business insights solutions.


Creating Calendar Duration for use with the Gantt Visual in Power BI

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:

    1. Open the Power BI report you’re using that contains the data from Project Online that you want to visualize with the Gantt visual.
    2. 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’.
    3. 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)

    1. 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.
    2. 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:

    Happy Reporting!

    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




Nate has been working with Microsoft’s cloud productivity and communications solutions since their initial release, and prior to that gained much of his expertise configuring, administering and developing solutions on the on-premises SharePoint, Exchange and Project Server platforms.  With a strong background in enterprise reporting, business process automation through SharePoint workflows, and project management using Microsoft Project, Nate helps Sensei’s clients realize significant benefits from the Microsoft business toolset.  Recently earning the Microsoft Certified Solutions Associate: Office 365, Nate is certified in the installation, configuration and architecture of the SharePoint and Microsoft PPM platforms both online and on premises, leveraging his proficiency to assist clients in their move to the Microsoft cloud. With dozens of successful Microsoft PPM implementations under his belt, Nate is an expert at distilling how Microsoft’s suite of products and features provide value to Sensei’s customers. He regularly presents at Microsoft and Microsoft Project User Group (MPUG) events.