SharePoint (and Project Server) Shenanigans

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

Whilst Microsoft’s Office 365 PPM tool Project Online supports projects using different currencies, there is no conversion based on a currency rate. The project has a currency set and the correct currency symbol is displayed for cost data. In the reports and views there is no conversion based on an exchange rate between currencies. This blog post shows a simple way using Power BI to have a report calculate the project cost based on the defined currency as seen below:

In the steps below we walkthrough how to set up this simple example. Firstly in the Power BI Desktop client add the Projects OData feed:

• Click Get Data > OData Feed and add the Odata URL for your PWA site: <PWASite>/_api/ProjectData/Projects and click OK
• Click Edit to launch the Power BI Query Editor then click Choose Columns and uncheck Select All to deselect all the columns then select at least ProjectName, ProjectCost and  ProjectType and click OK
• Click the dropdown menu on the ProjectType column and uncheck 7.
• Change the table from Query1 to Projects

The Projects table is now completed.

Now we need to create a table for the different currencies and rates we want to use.

• Click Enter Data to launch the Create Table window and create the columns “Currency” and “Rate”. Enter the data as needed and call the table Currency then click OK. I created the following:
• 
• On my PWA instance, GBP is the currency used for this demo / blog post so this is set to 1.00 then I have a example currencies / rates for Euros and USD

The Currency table is now completed. Click Close & Apply > Close & Apply. Create a table with the project data on then a slicer for the currency values from the currency table like below:

Now right click on the Projects table in the fields pane and click New measure:

Enter the following in the formula / query bar:

This gets the selected currency from our slicer as a value in the Projects table. This is then used in the next new measure we create. Right click on the Projects table in the fields pane and click New measure again:

Enter the following in the formula / query bar:

Now add the ProjectCost_Converted field into the table with the project data and change the slicer selection and notice the ProjectCost_Converted values change, as seen below for Euros (EUR):

For Pounds (GBP):

For US Dollars (USD):

This simple version doesn’t take into account for projects that span multiple years where different rates will apply, it could be extended to support that though. In part 2 later this week we look at pre-calculating the project cost so that the portfolio cost is correct based on the currency defined by the project, look out for that towards the end of the week.