The "only" Power BI calendar you will ever need

[ruthpozuelo]

Tired of creating Power BI calendars? Well, me too, so let's put a stop to that.

In this blog post you will learn how to create (or download mine) a proper calendar that ”self-generates” when you open the file and is configurable.

If you download mine, you most likely will need to add columns that are relevant to you, but using the methodology explained in the post, you will do it only once.

Note: You need to have the latest (june 2016) power bi update installed.

First, some of my "new" best practices with Power Query

I have been sharing a calendar in my YouTube channel to help others create their calendars fast, but I can’t say I was proud of that work. Here is how it looked like:

Those that downloaded the file, had a hard time to modify it (never got a complain, but still….), as it was done without any care from my side. I apologize in advance if you are one of those that downloaded the file!). The main reason why that file looked the way it did, was mainly because doing it properly took too much time, and when I wasn’t able to explain the queries…what was the point?

Well, with the recent Power BI updates, the Power BI team has given us the ability to do a proper job, especially if you are sharing your files with others.

Here is how my calendar file looks today:

And it self-generates!!!  (with some basic input from your side to fit your needs).

A good calendar, should be properly formatted, so here are my new new best practices when working in the query editor/power query: ( I say new because you need the latest update to use some of the features)

1. Rename all the query steps so other uses can quickly find them and modify them.

First of all, we needed to clean up the query steps, give them proper names.

There is more than one way to do this; Right-click > Rename or Right-click > Properties –(new with June update) but I prefer to click on the step and then press F2, it is faster.

2. Select the correct Data type for all columns

If you have been lazy like me, you might have left some data types default and got into trouble when trying to display the values in graphs.

But you haven’t missed the icons on the left of every power bi column right? With them, you can see easily see if a column has been left default and also you can change it to the right value easily.

I normally try to this transformation at the end in one step as it makes the file more readable. If I need to change the data type to reference it on another column, I use Number.ToText function.

For example:

I want to have leading zeros on my Day column. The function Date.Day returns a number, but instead of converting that into a number and then create the leading zeros I can do that in one step with the Number.ToText function:

Text.PadStart(Number.ToText(Date.Day([Date])),2,"0"))

The file becomes more readable without all those steps in the middle.

3. Reorder your columns to make the file more readable.

For example, in our calendar example, some of the steps create date columns and others create sort columns. I have regrouped them so they are next to each other.

You haven’t missed the new functionality that allows you to drag and drop the query steps right? Live saver!!

5 group similar queries

4. Add comments to your query steps to help others understand or modify your query

This is a new feature from the June (2016) update and here is a cool example on when this make sense:

Our calendar has a Day Name and Month Name column. Depending on which country you are at or how the report will be used, you might want those in your local language or not.

Power Query allows you to change the language by adding a country code at the end:

Date.ToText([Date],"ddd", "en-US"))

Now, if somebody downloads the calendar from me again, I want them to be able to modify this easily, so a proper text could be:

"Extract Day Name from Date column using American names. Navigate here for other Languages not included in the parameters: https://msdn.microsoft.com/en-us/goglobal/bb896001.aspx"

 

To add comments to your query steps, select the step and then Right-click > Properties. If you are adding a lot of steps, it might be easier to do it in the advance query editor. Read Chris webb post to learn how to do it.

 And the previous step leads us to this step which makes the calendar easily configurable:

5. Add query parameters to easily customize your calendar

Let’s use the previous example to explain this one. In the previous step, we hardcoded the language code, but why not using query parameters to easily change those values so the users don’t need to look for them?

Date.ToText([Date],"ddd",#"Language")

I have created 4 query parameters to configure the calendar to different regions and dates.

This calendar uses List.Date to generate the date table. The values to specify the dates you want your calendar to be are hardcoded in the Invoke step, so I created parameters for that: (Year, Month, Date). There is also a parameter to configure if your week starts on Monday or Sunday.

And now the icing on the cake.

Save your calendar as a Power BI Template

How about saving this as a Power BI template and use this as a new file instead of an ordinary one? I learned this from Ryan Dusty and you can check out other tips and tricks from him here.

It would be great if we we could save our templates as shortcuts on the Power BI ribbon....

So, if you save this as a template, when a user opens the file, this is what they get:

Isn’t that awesome?

You might need to modify this file to be able to use it, but once you created it, you won’t need to build another power bi calendar!!

Here is a demonstration of how the calender looks like and you will find there the link to download too.

Happy Analyzing!

[ruthpozuelo]

Hi @dbadmin!

Thanks! 🙂 

Really happy to be able to contribute to this amazing community!

Happy New Year!

/Ruth

[jason85]

It's important to use other type of calendars as well such as monthly calendars which will help in managing your time an work activities. Download March Calendar 2017, April 2017 Calendar and more.

[ruthpozuelo]

Thanks @Sanjeev787! Glad you found it useful 🙂

/Ruth

[satnam]

Very well done this is amazing, and please check ones these useful calendars

February 2018 Calendar

Printable 2018 Calendar

We hope this month will amaze you.

[satnam]

<a href="http://www.maxcalendars.com/printable-march-2018-calendar.html">Printable March 2018 Calendar</a>
<a href="http://www.maxcalendars.com/march-2018-desk-calendar.html">March 2018 Desk Calendar</a>

[satnam]

March 2018 Calendar

March 2018 Holidays

[calendar2019]

Calendar 2019

January 2019 Calendar

February 2019 Calendar

March 2019 Calendar

[ShreyasK]

Thanks @ruthpozuelo for wonderful calender trick. I am using this calender now in my dashboard but there is small problem. For current month, I am able to see date till yesterday only (29th Dec 2022). What can be done to see today's date (30th Dec 2022) in same calender. Can you please help.