PowerBI : manage different project calendars in your dahsboards.

Following my previous article talking about how to manage your calendar working time as a parameter in PowerBI, I had an interesting comment from my fellow colleague Xavier Trottin. Xavier has a similar role as me, but works most of the time with larger organizations. Meaning that they often (always) have different project calendars because they are managed in different countries with specific working week hours.

Thus my previous method doesn't work. It would require having as many parameters as calendars. 

The way Xavier proposes to manage this use case is to retrieve the minutes per day, which is a data based on the project calendar and attached to each project. The issue is that this data is not accessible in the standard OData reporting PROJECTS table (/_api/projectdata). You have to collect it from the /_api/ProjectServer/Projects table.

Here are the steps to perform to go further in your deployment industrialization.

1- Create a new query. In the source step of the new query, access the table mentioned above. Note that since the PWA url parameter goes until /PWA, you don't have to create a new parameter.

2- Select the ID and minutes per day columns. Note that you can find here a lot of useful information, such as "ischeckout" or the last saved date.

Then you'll get the query output as following, with the project ID which will be the key of the merge and the minutes per day (420 in my example for 7 hours/days).

3- Merge queries. From the standard PROJECTS query, merge it with the newly created query. You'll have to select the project ID from both queries to merge the tables.

4- Expand the column. I'm not really sure if this stage is mandatory but it allowed me having the minutes per day column in the standard PROJECT table in addition to the merged query. You do not need to include the ID column since it is only used for the merging step.

5- Use the new measure in the formulas. Back in the desktop client, for the project duration in days in my example, I divide it by the minutes per day multiplied by 60 (to get hours).

Et voilà! Thanks again to Xavier for the tip. And remember that those improvments have a considerable added value since you do it on one report and you can deploy it for any customer.