Share via

Power BI: Determine The Project Status Based on the Project Start & Finish Date Using Calculated Column

Introduction

In this article, we will explain How to create a calculated column in Power BI to determine the Project Status based on the Project Start and Finish Date.

https://gallery.technet.microsoft.com/site/view/file/180178/1/PowerBI%20Determine%20The%20Project%20Status%20Based%20on%20the%20Project%20Start%20and%20Finish%20Date.gif

Note: In this example, we are working on Project Server Database as a Datasource in Power BI

https://gallery.technet.microsoft.com/site/view/file/180179/1/Power%20Bi%20Data%20Source.png

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.
  • Select your Table or view > Click Load > The fields would be now shown.

https://gallery.technet.microsoft.com/site/view/file/180200/1/PowerBI%20Create%20a%20datasource.gif

  • 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]<TODAY(),"Finished","Not Set")))

https://gallery.technet.microsoft.com/site/view/file/180201/1/PowerBI%20Create%20Calculated%20Column.gif

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

https://gallery.technet.microsoft.com/site/view/file/180202/1/PowerBI%20Create%20Table.gif

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

https://gallery.technet.microsoft.com/site/view/file/180203/1/PowerBI%20Create%20and%20format%20Chart.gif

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.

Download

​Download the Power BI file from GitHub.

See Also

Back To Top