Draw S-Curve on Power Bi with XER/XML

While all of us transfer to use Power Bi for data visualization for projects we try to find the most suitable and easiest method to connect our dashboard to primavera p6, for sure connection with XER and XML are most common methods as no need to do any in-between steps every time we update the schedule as just put the new XER/XML files in the data source folders will update our dashboards.
One of the most common issues in this method that the distributed values for resources are not saved in both output files so all of us can not draw the S-Curve or deal with the time distributed data for resources.

To Solve this issue a word around module had been developed to generate the time distributed data for resources based on the Start and Finish dates for activities.

Through the next steps I’m going to show you how to do that

First, we will use a simple data source contains only thew next data.

1- Activity Id

2- Activity Name

3- Start Date

4- Finish Date

5- Planned Value

6- Earned Value

Those data are exported as excel sheet from primavera p6 just to simulate the data exists in XER/XML.

Second, after connecting these data with Power Bi will start to build our queries to generate the time distributed values. all steps in the module attached can be easily understood but we will concentrate on how we will generate the distributed values.

• while the activities have only one value for the earned value and planned value
• we will calculate the number of days between the start date and the finish date for every activity using add custom column.
  

• Then we will calculate the values for every day as shown in the below formula
  = Table.AddColumn(#"EV-Day", "PV-AmountDay", each [#"Planned Value Cost($)"]/[Duration])
• then we will ask power bi to create a list of values contains same number of days from the daily value of resource, for example if the Earned value of activity is 10,000$ and the duration is 10 days then the value of everyday 1000$.
  power bi will create list of values contains 10 records each one equal 1000.
  {1000,1000,1000,1000,1000,1000,1000,1000,1000,1000}
• Last step we will ask Power Pi to expand these lists, which will generate new rows for every activity, number of rows equal to the activity duration.

From the below image you can see how the distributed value calculated

I hope this method to help and please don’t hesitate to contact me for any questions.