How to Create Planned Value Vs Earned Value Report in Power BI

Introduction

Tracking project performance is critical in project management, and two key metrics that help in monitoring progress are Planned Value (PV) and Earned Value (EV). These values provide insights into whether a project is on track, ahead, or behind schedule.

While Primavera P6 is commonly used for project scheduling, integrating PV and EV into Power BI allows for real-time analysis and visualization. In this guide, we will create a Planned and Earned Value Dashboard in Power BI using Primavera data. By the end of this tutorial, you will be able to:

· Import Planned Value (PV) and Earned Value (EV) data from Primavera P6 into Power BI

Ready to take your skills to the next level? 🚀 Check out our Special Online Courses! 🚀
Gain valuable insights, boost your career, and learn from industry experts.

Planning Courses

· Create a clustered column chart comparing PV and EV

· Develop a cumulative line chart for tracking overall project trends

· Calculate and visualize the Schedule Performance Index (SPI)

· Use a Card Visual to indicate if the project is Ahead, On Schedule, or Behind

· Add Text Box for the Report Title

· Add Cards for the Sum of Earned Value (EV) and Planned Value (PV)

This is the report. You will be able to create it at the end of this article.

Note: We use the Actual Cost as the Earned Value does not exist in the resource assignment so here, we have the Actual Cost equal to the Earned Value but if the Earne

Value does not equal the actual cost you check Actual cost not calculated automatically Article

We also have plenty of articles about Power BI, Like:

1. Simple Dashboard

2. Simple Procurement Log

3. S-Curve in Power BI

Step 1: Preparing the PV and EV Data

Before importing data into Power BI, we need to ensure that our Excel file exported from Primavera P6 is structured correctly.

From Resource Assignment, Click on Display and choose Spreadsheet Fields then Choose Customize.

Choose these Columns, Actual Cost & Budgeted Cost then Click on Okay

This is what we need from Primavera P6

Copy and paste it into Excel, We will fine-tune this data in Excel and remove all the unnecessary data

Here we have the Actual Cost for the Earned Value and the Budgeted Cost for the Planned Cost.

Ensure that the data is structured properly in Excel to avoid formatting issues when importing into Power BI.

This is the data we need from Primavera P6

Step 2: Importing Data into Power BI

1. Connecting Power BI to Excel

Open Power BI Desktop.
Click Get Data → Excel.

3. Select your Excel file and click Transform Data

This is the Excel file imported to Power BI

1. 4. From the Transform Menu, Choose Transpose

1. 5. From the Home Menu, Use the First Row as Headers.

1. 6. Rename the Spreadsheet Field to Dates then Close & Apply

Now, your Planned Value and Earned Value data are ready for visualization.

Step 3: Creating Planned Value and Earned Value Visuals in Power BI

Creating a Clustered Column Chart for PV and EV

Click on the line and Clustered Column Chart in the Visualizations Pane.

2. Drag the Date to the X-axis.

3. Drag Planned Value (PV) and Earned Value (EV) to the Y-axis.

4. Format the Date

5. This is the Visual before adding the cumulative Line

This visualization helps track actual project progress vs. planned progress over time.

Creating a Cumulative Line Chart for PV and EV

To track the total progress of the project, we need to create cumulative values using DAX Measures.

Creating Cumulative Measures for PV and EV

Click on New Measure in Power BI.

2. Type the Cumulative EV Measure:

1. CumulativeEV =

2. CALCULATE(

3. SUM(‘Sheet1′[Earned Value]),

4. FILTER(

5. ALLSELECTED(‘Sheet1′[Date]),

RECOMMENDED COURSE

Power Bi Monitoring and Controlling with P6

Microsoft Power BI is very easy software. However, it is magically solved the slow performance of excel for large data, added new…

1,200 students
WhatsApp support group
Certificate

View course

6. ‘Sheet1′[Date] <= MAX(‘Sheet1′[Date])

7. )

8. )

3. Create the Cumulative PV Measure:

1. CumulativePV =

2. CALCULATE(

3. SUM(‘Sheet1′[Planned Value]),

4. FILTER(

5. ALLSELECTED(‘Sheet1′[Date]),

6. ‘Sheet1′[Date] <= MAX(‘Sheet1′[Date])

7. )

8. )

Explanation:

CALCULATE(…)
- This applies the filtering logic to SUM(‘Sheet1′[Earned Value]), meaning it sums the Earned Value from the beginning up to the current date.
SUM(‘Sheet1′[Earned Value])
- This part calculates the total Earned Value for the current context (e.g., a row in a table or a date on a chart).
FILTER(ALLSELECTED(‘Sheet1′[Date]), ‘Sheet1′[Date] <= MAX(‘Sheet1′[Date]))
- ALLSELECTED(‘Sheet1′[Date]): This removes any filters on the ‘Sheet1′[Date] column except those applied externally (e.g., slicers).
- ‘Sheet1′[Date] <= MAX(‘Sheet1′[Date]):
  - It keeps only the rows where the Date is less than or equal to the current row’s date.
  - This ensures that all previous and current dates are included in the cumulative sum.

Add Cumulative PV and Cumulative EV from the data on the Line Y-axis

Here we will start Editing the Chart, the colors, name, and data labels

1. Change the Visual Name from Format choose General

At Title Type the wanted Visual Name

2. Show the Data Label form Format and choose Visual

Then turn the data labels on to see all the Labels

3. Change the Column Color from Visual and choose Column

Then choose the series to select which column to change its color

4. Change the color of the lines from Format Visual

As you can see here choose which line do you need to change its color

Step 4: Tracking Schedule Performance Using SPI

1. Calculating the Schedule Performance Index (SPI)

The Schedule Performance Index (SPI) is a key metric that indicates whether the project is ahead or behind schedule.

Formula for SPI: SPI=EVPVSPI

To create SPI in Power BI:

Click Quick Measure.
Choose Division

1. 3.Choose the Numerator and Denominator according to the SPI equation

Once you add the division you can see all the columns in the Model to choose which Columns will be put in the Numerator or Denominator to calculate the formula we want to achieve.

We will choose the Cumulative PV and EV to calculate the over all project SPI

Then double-click on the newly created column to change its name as follows

2. Adding SPI to a Gauge Chart

Select Gauge Chart from the Visualizations Pane.

2. Drag SPI Measure into Values.

3. Set the Minimum value to 0 and Maximum to 2.

From Format Visual go to the Guage axis and but the Min Value to be 0, the Max Value is 2, and the Target is 1

If SPI > 1, the project is ahead of schedule so the Gauge chart will give us the green color

If SPI < 1, the project is behind schedule so the Gauge chart will give us the red color

1. From Format Visual go to Color

You can find the fx beside the color to make the above formatting

So we will choose the same Values for minimum and maximum with the previously explained Color theme. Then Press on Ok

This is the Final Product of the visual and this means that the project is behind schedule.

This visual provides an instant assessment of schedule performance.

Step 5: Creating a Card Visual for Project Status

1. Defining Project Status in Power BI

We will classify the project into three categories:

Ahead of Schedule → SPI > 1
On Schedule → SPI = 1
Behind Schedule → SPI < 1

2. Creating a DAX New Measure for Project Status

SPIText =

SWITCH(

TRUE(),

[SPI] < 1, “Behind Schedule”,

[SPI] >= 1 && [SPI] <= 1, “On Schedule”,

[SPI] > 1, “Ahead of Schedule”,

“No Data”

)

Explanation:

SWITCH(TRUE(), …)

The SWITCH function in DAX works like a CASE statement in SQL or a series of IF conditions.
TRUE() is used to create a flexible condition where each condition is evaluated in order, and the first one that matches is applied.

Conditions and Outcomes:

[SPI] < 1 → “Behind Schedule”
- If the Schedule Performance Index (SPI) is less than 1, it means the project is behind schedule.
[SPI] >= 1 && [SPI] <= 1 → “On Schedule”`
- If the SPI is equal to 1, it means the project is on schedule.
[SPI] > 1 → “Ahead of Schedule”`
- If the SPI is greater than 1, it means the project is ahead of schedule.
Default case → “No Data”

If none of the conditions match (e.g., SPI is blank or an error occurs), it returns “No Data”.

3. Adding a Visual Card

Select Card Visual from the Visualizations Pane.

2. Drag SPI Text Measure into Fields.

Turn off the Category Label to remove the SPIText from the Card

Now, the dashboard will dynamically display the project status.

Step 6: Add Text Box for the Report Title

From the insert Menu choose the Text box

Type “Project Status Report”

Step 7: Add a Card Visual for PV and EV

From Build your Visual Add a card and Put the Planned Value in the Field, We will do the Same for the earned value

Now the Final Step is to put Every Visual in its place to see how the Report will look after editing.

Let’s make sure that all the Visuals are working properly.

As you can see here, we clicked on the DEC chart so we have here that the Earned Value is greater than the planned so the SPI is greater than 1 then the Project is Ahead of schedule.

You can find all the Article Resources in our POWER BI Course Library

Conclusion

By integrating Planned and Earned Value from Primavera P6 into Power BI, project managers can: Monitor actual vs. planned progress Identify delays early and take corrective actions Improve decision-making with real-time insights With this Planned Value and Earned Value Dashboard, you can track project performance in a structured and dynamic way.

Download the Article Resources