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.


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

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.
  • 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")))

  • 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 Project Name at Donut chart values.
  • From the Fields Pane > Add the new Project Status calculated column at Donut chart Legend. 



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.

See Also


Back To Top