How to Calculate Project Status In Power BI

In this post, we’ll explain how to Calculate Project Status In Power BI from Project Server 2016 database based on the Project Start and Finish Date.

You might also like to read Install and Configure Power BI Report Server On-Prem.

---

Project Status Calculation in Power BI

As we know, one of the main KPI that we need to measure In Project Server 2016 is the “Project Status” KPI which calculated based on the built-in fields Project Start Date and Project Finish Date.

So to calculate the Project Status based on the project duration, you should apply the below conditions:

• If the Start Date>Today(), therefore the Project Status will be “Not Started”.
• If the Start Date<=Today() && FinishDate>=Today() , therefore the Project Status will be “In Progress“.
• If the FinishDate<Today(), therefore the Project Status will be “Finished“.

---

Get Data from Project Server Database in Power BI

In this section, we’ll get data from the Project Server database as a data source to can use it in Power BI Desktop.

Note: In Project Server 2016, there is no Project database like Project Server 2013, It’s only a single database (SharePoint Content Database) holds the project data and the content to facilitate the database operations, such as backup and restore, migration, etc.
For more details, please check, New and deprecated in Project Server 2016?

Steps

• Open Power BI Desktop.
• At Home tab,> Click on Get Data > Select SQL Server Data Source.
• Provide the SQL Server instance and optionally add the Database Name.

Note: You will be asked to provide the right credentials, If the current user doesn’t have permission to access the database.

• Search for the “MSP_EpmProject_UserView” view.
• Click the “Load” button to load the data view.

Create Calculated Column in Power BI

• From the above ribbon, > at Home tab, > Add a new Calculated Column.
• A column formula would be shown to write the Project Status formula based on the Project start and end date.
• Type the column formula based on your data source name and column name as below.
  • The Project Status equals “Not Started“, in case
    • [ProjectStartDate]>TODAY().
  • The Project Status equal “In Progress” in case
    • [ProjectStartDate]<=TODAY()) && [ProjectFinishDate]>=TODAY()
  • The Project Status equal “Finished” in case
    • [ProjectFinishDate]<TODAY()
  • Else “Not Set”.

Download the Power BI file and the formula from GitHub.

• From the Visualization Pane, Add a table.
• From the Fields Pane, Add the Project Fields as you prefer at the Table Value.

• Again from the Visualization Pane, add Donut chart.
• From the Fields Pane, add the count of Project Name to Donut chart “Values” section.
• From the Fields Pane, add the new Project Status calculated column at Donut chart “Details” section.

---

Conclusion

In conclusion, we have learned how to

• Create a data source from SQL Server Database in Power BI.
• Create a calculated column in Power BI.
• Use Nested If in Power BI calculated column.
• Calculate The Project Status In Project Server based on the Project Start and Finish Date.

Download

Download the PowerBI file from GitHub.

Project Center Totalize

Add Project Center Totalize Google Extension to get the count of projects in Project Center and show Projects per status.

Applies To

• PowerBI.
• Project Server 2016 Database.
• Project Server 2013 Database.

You may also like to read

• Install and Configure Project Server 2016.
• Install and Configure Power BI Report Server On-Prem.
• Export Enterprise Custom Fields to Excel file in Project Server 2016.
• Query Enterprise Custom Fields from Project Server Database
• New and deprecated in Project Server 2016?

Have a Question?

If you have any related questions, please don’t hesitate to ask it at deBUG.to Community.