Friday, December 15, 2017

PowerBI report pack V2 for Project Online: add the weekly timescale

I'm not a developer guy, thus I'm quite happy with PowerBI. I can do nice reports and have the feeling that I actually developed something. Microsoft provided a report pack updated (v2) which has been blog quite all over the web. But what I found (and my customers too) is that the monthly timescale for the capacity planning was too high level and they need to bring a more granular look to the resource capacity planning.

Of course, we all know that since a couple of weeks, you can choose the reporting granularity. But if like me you are not willing to update and migrate all your reports impacted by this change, you might just want to keep your original reports and add to them the weekly dimension.

So here is what they would like to have. Note the weekly timescale.

Nice isn't it? So how to get there starting from the report pack:

In the query editor, edit the TIMESET table, then the "removed other columns" step to add the week number.

Since this is a one-digit column, for the ordering to be done correctly (1, 2, 3...) and not (1, 10, 2, 3...), you need to force the week number to be 2 digits. Create a custom column with the following formula: 
Week = Text.PadStart(Text.From([TimeWeekOfTheYear]),2,"0")

Insert a new field extracting the 10 first characters of the date, then the 4 last characters to extract the year:

Finally concatenate the year and the week number doing a merged new column:

Apply the changes, and now you are good to use this new weekly time dimension in any of the visuals.

Share this article :

1 comment: