Power BI: Calculate The Project Status In Project Server

In this article, I will explain

How to Calculate The Project Status In Project Server based on the Project Start and Finish Date In Power BI?

Power BI: Calculate The Project Status In Project Server


Scenario

Using Power BI, we need to calculate the Project Status

  • Not Started.
  • In Progress.
  • Finished.

Based on the Start and End Date of projects in In Project Server.

Power BI: Calculate The Project Status In Project Server


Steps

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

Note: If the current user doesn’t have permission to access the data source. You will be asked to provide the correct credentials.

The user was not authorized

  • Select your Table or view > Click Load > The fields would be now shown.

PowerBI Create a Project Server datasource

  • From the above ribbon, > at Home tab, > Click on New Measure > New 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 equal Not Started” in case
      • The StartDate is Greater Than Today.
    • The Project Status equal In Progress” in case
      • The StartDate is Less Than Today && the FinishDate is Greater Than or Equal to Today.
    • The Project Status equal Finished” in case
      • The FinishDate is Less Than Today.
    • Else “Not Set”.
Project Status =
IF('MSP_EpmProject_UserView'[ProjectStartDate]>TODAY(),"Not Started",
IF(('MSP_EpmProject_UserView'[ProjectStartDate]<TODAY()) && ('MSP_EpmProject_UserView'[ProjectFinishDate]>=TODAY()),"In progress",
IF('MSP_EpmProject_UserView'[ProjectFinishDate]<span 				data-mce-type="bookmark" 				id="mce_SELREST_start" 				data-mce-style="overflow:hidden;line-height:0" 				style="overflow:hidden;line-height:0" 			></span><TODAY(),"Finished","Not Set")))

PowerBI Create Calculated Column

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

PowerBI Create Table

  • Again from the Visualization Pane > Add Donut chart.
  • From the Fields Pane > Add the Project Name to Donut chart values.
  • From the Fields Pane > Add the new Project Status calculated column at Donut chart Legend.

PowerBI Create and format Chart


Applies To
  • Project Server 2016 Database.
  • Project Server 2013 Database.
Conclusion

In this article, 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.
  • Create a Table in Power BI.
  • Create and format Donut chart in Power BI.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s