How to Calculate Project Status In Power BI

power bi calculate project status in project server

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.

How to Calculate Project Status In Power BI
power bi calculate project status in project server

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.

Power BI: Calculate The Project Status In Project Server

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“.
Calculate The Project Status In Project Server In PowerBI
How to Calculate Project Status In Power BI

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.

The user was not authorized in Power BI
  • Search for the “MSP_EpmProject_UserView” view.
  • Click the “Load” button to load the data view.
Create a Project Server data source in Power BI

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.

Get MSP_EpmProject_UserView view from Project Server in Power BI
  • From the Visualization Pane, Add a table.
  • From the Fields Pane, Add the Project Fields as you prefer at the Table Value.
Show MSP_EpmProject_UserView in Power BI
  • 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.
How to Calculate Project Status in Power BI

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.

Calculate Project Status In PowerBI
How to Calculate Project Status In Power BI
Project Center Totalize

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

count the number of total projects in the Project Center
Applies To
  • PowerBI.
  • Project Server 2016 Database.
  • Project Server 2013 Database.
You may also like to read
Have a Question?

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

Leave a Reply