In PowerBI reports, I usually propose a set of dashboards including standard indicators. On a portfolio dashboard such as the one below, you'll always find the duration, baseline, variance, etc...
By default, any data related to time unit in the reporting database is expressed in hours. When you talk about work, it might relevant, but duration are most of the time more comprehensive in days.
In my first reports, I used to create a new column with a formula dividing the duration by the number of hours in a working day.
When you have one major Project deployment per year, this can be a suitable solution. However I personally saw the change in our Project consultant job: with Project Server, we used to do major deployments which could take months and even years. Now with Project Online, since you don't manage the infrastructure part, deployments are much more simpler and quicker. Moreover much more organizations are interesting in deploying Project Online since it is now opened also to smaller companies (less licensing fees). Therefore we are doing a large number of smaller deployments.
All that to say that with this new paradigm, we do need to industrialize our deployments and particularly PowerBI reports deployments. In the usual use cases where the working days can be different in all organizations, the method described above can be time consuming.
So here is the solution: set the working time as a variable.
1- In the PowerBI Query Editor, select "new parameter". You can create a new parameter "hrs_day" with a list of values (7, 8, etc...) and select the current value for the company you're deploying Project Online.
Note that I already have the PWA tenant URL as a parameter.
2- In the dataset where you need to convert hours into days (Project dataset in my example), add a custom column calling the parameter.
3- Back in the desktop client, select again the Projects dataset and select the measure having the manual calculation (.../7). You can also create a new column. Replace the division by 7 by the new parameter.
Here is how to manage working time as a parameter in your PowerBI dashboard. This will optimize your report deployment for new customers and make you reporting toolbox more efficient.
Hi Guillaume! As you mentioned, parameters are really helpfull.
ReplyDeleteThat is more complex if you implement Project Online for a huge organization with a mix of projects calendar (most of the time 7h and 8h per day).
Hopefully, you can also directly retrieve the "number of minutes" per day for each project plan. If one project A is using 420 min (7h) per day, and project B 480 min (8h)/d, your duration in days will be calculated properly for each project... whatever the "standard" number of hours expected.