Showing results for 
Search instead for 
Did you mean: 

The "only" Power BI calendar you will ever need


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:


1.calendar before.png


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:


2 calender after.png


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.


3 rename query steps.png


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:




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


4. group chages if possible.png



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:"


 6. query comments.png

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?




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


7. query parameters.png


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:


9 ask for query parameters.png


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.


10 youtube.png


Happy Analyzing!


Super post Ruth. A great reminder of some new features and you've pulled together those best practices nicely! 

Thanks @Brian_M!



 Super post Ruth. It's amazing! I try to do the same by my own , but it is very complicated for a beginner like me so I copy it. Nice job

Thanks @ThomasTurbado! 🙂





Great post. Thanks Ruth.

Thanks to everybody for all the comments and kudos!  🙂


Hi Ruth, can we have this adapted to deal with custom calendars like retail 4-4-5 etc?

Hi cenewell, 

It should be possible. What are your rules for the calendar? I can give it a try!


Hi Ruth, it's a retail calendar arranged in 4-4-5 format

There is additional complication in dealing with with leap years

Here is a definition:

ok, so a "standard" 4-4-5 calendar works fine for you? Give me a few days and I let you know.


This is really great way to make your date dimension table.  Love the technique, and may even adopt it over some of my other methods. At least this is a really nice post that I can refer people to, who are struggling with it.  I really like your naming convention using the "sort" columns, etc.  I never know what to call mine, and really enjoyed seeing how you handled it.


Thanks for the post.

HI Bryant,

As english is not my native language I often struggle to find good names to my columns too!

Thanks for taking the time to give me that detailed feedback, I really appreciate it 😉


Hi @cnewell7,


I created a calendar template that generates different custom calendar types: 445 calendar, 454 and 544. Check it out and let me know if that is what you wanted to do!



Hey Ruth! 🙂 Thanks so much for this! Works great. Keep up the great work. Enjoy your blogs also!