Build a Reusable Calendar Table with Power Query

Original Post : Matt Allington, 24 Apr 2018

Background

Technically you don’t need calendar tables to use Power BI, but for most reports that go beyond a simple Excel spreadsheet analysis, Calendars are an essential component of a successful Semantic Model.  There are many different approaches to creating and maintaining a Calendar table for your model, but I believe Power Query is the best tool to solve this problem (and so does Jeffrey Wang (reference)).  As a result, I have decided to provide this Hitchhikers Guide to the Power BI Calendar.

Matt originally posted about this idea many years ago over at PowerPivotPro.com and then provided the original how-to guide here in 2018.  I have learnt a lot about Power Query and specifically what makes a good Calendar table since that time, plus Power Query has come along in leaps and bounds, and it now has a lot more capability than it had previously.  Today I am going to write a revision of this post showing you how to create a calendar table in Power Query.  Once you know how to build a query like this, you can copy and paste the code for it into a text storage tool (like OneNote or Notepad) and reuse it over and over for all of your data models – very cool.  Plus you just might learn a few new Power Query tricks along the way.

The UI is Your Friend

Sure, you can do more when you can code in M directly, but one of Power Query’s greatest strengths is you don’t need to be a programmer to achieve amazing things with your data preparation.  As a result, my approach to teaching people to use Power Query is to always use the UI where possible.  I first use the UI to do the hard work, then jump in and make small changes to the code created by the UI to meet any specific variations required.  Keep this concept in mind as you read this article.

To teach you how to create a calendar (dates) table in Power Query, I am going to use Power BI Desktop, but of course Power Query for Excel or Dataflows online will work just as well and the process is identical.  In fact the calendar query at the end can easily be cut and pasted into your tool of choice, but if you jump straight to the end, you miss a great opportunity to expand your learning.

Let’s begin.

Step 1: Create a list of dates

Starting with a blank Power BI report, to create a calendar table in Power Query, I’m going to begin by creating a blank query.  Navigate to the Home ribbon, select the dropdown list for Get Data, and then select Blank Query.  This simply creates a new query with no data in it, and importantly no data source.

Let’s rename the query and give it the better name, “Dates” or “Calendar” if you prefer.

Note: “Calendar” is a reserved word in DAX, which means your table will be identified as ‘Calendar’ in your DAX formulas.  There are no issues with this, just something to be aware of.

Next, we’ll need a list of dates to build the calendar from.  In the formula bar of your query, call the function List.Dates into the formula bar without any parameters (and no brackets) as you can see below.

• If you can’t see the formula bar, you can turn it on in the View ribbon.
• Be aware that the M language is case sensitive, so list.dates will not work – you must type in List.Dates¹

Now of course you need to “know” that this formula exists to be able to use it – but now you know, so you can use it to create your calendar table in Power Query.

¹ Note: M Intellisense was made generally available to Power Query in April 2019, and has made formula writing significantly easier

 

The M language is self-documenting.  As you can see in the image above, when you enter the function name without any parameters and without the brackets, the UI will display the documentation that shows you how to use the function.  To take advantage of this function, we simply need to fill in the required parameters.  The values I entered into the parameters wizard can be seen in the image that follows, then click the button to invoke the function.

After doing this, something strange happened.  A new query was created (two below) and this new query passes the parameters to the first query (shown in 3 below).  Let me explain this further.

The first Query (shown as 1 below) Dates has 1 line:

= List.Dates

The second Query (shown as 2 below) called Invoked Function also has 1 line:

= Dates(#date(2025, 1, 1), 100, #duration(1, 0, 0, 0))

As you can see in the code above, the second query is simply passing the set of parameters (highlighted in blue below) to the first query, which is the function itself.  This can be simplified by passing the parameters directly to the function itself.  The word Dates in the formula below (3) is therefore not a function, but a reference to the Dates query that contains the name of the function (1).

Copy the code between the brackets (…….) from the Invoked Function query (2 above) and paste this into the source step of the Dates query (1 above) as shown below.

Having done this, you will notice that The Invoked Function query is in error.   That is fine, we’re now calling the function directly in the Dates query, so the Invoked Function query is no longer required, and can be deleted.

Note: The benefit of invoking the function is that it manages all the syntax and parameters automatically for you using the UI wizard – much easier than typing this all in by hand, but now that you know how the List.Dates function works, you have the choice to provide the parameters directly in future if you prefer.

At this point, our calendar is a single query, with a single Source step.  Next we need to perform the following steps:

• Convert the “list” into a “table” using the “To Table” button in the List Tools ribbon.
• Double-click on “Column1” and rename to “Date”.
• Change the data type to Date.

We now have a Date column that spans 100 days from January 1, 2025.

Step 2: Allow The User to Set the Start Date and End Date

I want this to be a reusable Calendar table, and that implies that I will want different start dates for different uses.  Also I want this calendar table to “grow” over time so I don’t have to keep setting a new end date.  I refer you back to the formula in the source step above:

= List.Dates(#date(2025, 1, 1), 100, #duration(1, 0, 0, 0))

Note that the start date is currently hard coded as 1/1/2025 and the calendar is set to load 100 days. Below I will show you how to make the start date easier to maintain and the end date automatically set to the end of the year from the current date.

Start Date

To the left of the formula bar, click the ƒx (Add Step) button (1 below).  This added a new step called Custom1 (2 below).  Note how this new step automatically references the previous step (3).  This is the default, but it doesn’t have to be this way.

Replace the entire code in the formula bar (including the = sign) with the date you want your calendar to start with (1/1/2025 in this case).  Right-click on the Custom1 applied step and rename it StartDate.  Note there is no equals sign used, just the date entered as 1/1/2025.  This new step called StartDate is a hard coded step in the query that stores the required start date as a scalar value. You can tell it is a scalar value because the results pane (preview window) below the Formula Bar is displaying the scalar value, not a table (1 below).

Note : It is possible to use a parameter instead of a custom step, but this would create an additional query, and our goal is to produce a calendar that can be generated from a single query

This new step StartDate can be referenced from inside any other step in the query.   Return to the Source step, and replace the originally hard coded date with the reference to the StartDate step as shown below.

The query still works of course.  Note that the Source step is now referring to the StartDate step, yet the StartDate step doesn’t appear until way down the list of steps.  This is perfectly fine in the M language – the steps do not have to be executed in a linear order.

End Date (Length of Calendar)

Looking back at our original function List.Dates, you will note that the first parameter asks for the Start date, but the function does not ask for an end date for the calendar.  Instead, we have to specify how many periods (days in this case) the calendar should run for.  I want my calendar to run up to the end of the current year, and when we cross over to next year, the calendar will need to automatically update to the end of next year.  This implies I have to find out the current year’s last day dynamically, to do this we will rely on today’s date.  Once we have discovered the last day of what ever year we are in, we will then subtract the StartDate value from it, and hence calculate how many days are required in the calendar.

Return to the StartDate applied step, and add a new custom step by clicking the ƒx button again.  Replace the formula with the M function DateTime.LocalNow without any parameters.  (remember, if you just type the function name, the documentation will appear.

From the documentation provided by Power Query, we can see that there are no parameters required for this function.  It should be noted however, that when invoked, the function acquires the date and time from the machine running the instance of Power Query.  While we are working in Power BI Desktop, that is fine, the local machine’s time is right now, but if you intend to publish this calendar into the Power BI Service as part of a report’s Semantic Model, the date and time will be sourced from the cloud-server machine refreshing the data, and the Power BI Service’ time is Coordinated Universal Time (UTC) which for me is -10 hours of the current day time.  To compensate for this, we will set and adjust our time based on UTC, that way it will always be correct, irrespective of local or service refresh.

Replace the function in the formula bar with the DateTimeZone.UtcNow function instead.

Now, simply add open-close parentheses () to the end of the function to return the current date/time.  You can see once again that this is a scalar value (not a table) because you can “see” the value below the formula bar, however you will note that it is returning the time in UTC not the current local time. You will also note that the date time value has a +00:00 at the end.  This is because this is actually a date time zone value, and because of this we are able to convert it to our local time with a special function called  DateTimeZone.SwitchZone.

Replace the DateTimeZone.UtcNow() with DateTimeZone.SwitchZone to discover what this function does.

The DateTimeZone.SwitchZone funtion allows 3 parameters.  The first parameter is the date time zone you wish to change, the second is how many hours do you wish to shift, and the 3rd is how many minutes do you wish to shift. As I am based in Brisbane, Australia I have a local time zone of UTC + 10, so I will simply update my code as follows².

= DateTimeZone.SwitchZone( DateTimeZone.UtcNow(), 10)

² Note: Brisbane is the capital city of Queensland, and Queensland is unusual in that we do not implement a Daylight Savings time shift during summer, so the above code is infact sufficient for always returning the current date time in Brisbane, but most people live in a region where Daylight Savings is applied, and thus this formula is insufficient.  For those of you who need a Daylight Savings sensitive time capture, a significantly more complex formula will be required.  I will address solving this when we have a couple more tools at our disposal.

We now have the current date time in our local date time.

The next problem is that this is Date/Time format, but we need Date format.  To solve this, wrap a Date.From() function around the code to extract the date portion (shown below), then rename this step “Today”.  Each time the query is refreshed, this step will automatically update to reflect the current date.

We now have a hardcoded StartDate, and a dynamic ability to detect today’s date.  To calculate how long our calendar should run for we will need to first determine when our calendar should end.  If you would like your calendar to end at the current date, you can skip this next task.  If you would like your calendar to have the ability to also handle future dates, we will need to assign an appropriate end date.  I am going to set the end date as the last day of the current year.

Add a new step and wrap the formula as follows.  Also, rename the step EndDate.

= Date.EndOfYear( Date.AddYears( Today, 0) )

You may notice in the formula there are actually two functions wrapping Today.  The Date.EndOfYear is providing the last day of the year of the date provided.  The Date.AddYears is allowing the ability to shift your calendar further than this year if necessary.  For example, if I wanted the end of next year, I could simply replace the 0 with 1, and then the date would become 31/12/2026.

Next, add a new step and subtract StartDate from the EndDate³ to get the duration.

 

³ Note: If you chose to end your calendar at Today, use Today in place of EndDate

Just by looking at the output above, you can see that there is some strange data format returned.  This data is in the format DDD.HH:MM:SS.  To determine the run length of the calendar, we need to add this number of days to the StartDate, but this “duration” format won’t currently work.  This is because the StartDate is type date, but this new Custom1 step is type duration, so first we need to extract an integer portion of the duration, so it contains just the number of days.  This can be achieved by wrapping the formula in a function called Duration.Days below.

Here is the new code⁴.  I also renamed this step to “Length”.  You can see in the image below that this is now an integer value.

⁴ Note: Actually, this code will grow up to the 30th of December (or yesterday’s date if you are using Today).  You can simply add +1 after the close bracket to make it grow up to and including the EndDate.

We now have all of the parameters we need to build our dynamic calendar table.  Return to the Source step to change the first line of code to use the Length instead of the hard coded 100 days.  You can see the final version of the Source step below.

Great.  Now the user can manually change the StartDate step to any date, and the calendar will auto grow in length up to our specified EndDate.

Step 3: Build out the Calendar

Most of the hard work needed to create a calendar table in power query is already done.  Time to add the columns needed in the calendar.  Return to the last step in the query (Length) – “Houston, we have a problem”.  Note below that all I have on the screen now is the length of the calendar.  But I need “the calendar”, not “the length of the calendar”.

This is an easy fix.  Remember I said above that the steps don’t have to flow in order.

Begin by adding a new custom step, then change the code to refer to the last step that actually showed the calendar.  This new custom step (1 below) “reaches back” up the query steps and “grabs” the calendar in the state that it last existed (2 below) and makes it the last step again.

Add the Year

To add the year, click on the Date column (1), selected Add Column (2), Date (3), Year (4), Year (5).

What About Financial Year?

The default year column is calendar year.  But many companies have a financial (or fiscal) year.  In Australia at least, most companies have a financial year starting on 1 July.  Here is a neat trick I learnt from Matt.  The trick is to add 184 days to the date before extracting the year – it always works as long as your financial year starts on 1 July.

When I tried to do this the first time in the code, I got an error.

The reason is because you can’t add an integer to a date in Power Query like you can in Excel.  But if you take a look back at the first step (Source) again, you will see the syntax that is needed.

Power Query will only add the days after they are converted to a “duration”.  When I made this change, it worked for me as you can see below.

My new code (1 above) correctly calculates the financial year (shown in 2).  Note I also changed the default column name to “Fin Year” to avoid confusion with calendar year.

Add Other Columns

You can repeat the process of adding as many other columns you like, including Month Name, Month Number etc (using Add Column\Date from the menu).  I have done this in my Power Query calendar table but haven’t shown the steps in this article.  You can do this now or wait till we bring it all together at the end.  There are still a few extra transformations I would like to bring to your attention before we complete the calendar.

M Language Hacks

As you have already seen, I like to hack the code automatically generated by Power Query using the formula bar.  Below is a good example.  When I added the day number of week, the days were listed as 0 – 6 (Power Query uses a zero based index system).  I prefer to have the days of week numbered 1 through 7, and optimally I like Monday to be day 1.  I could add a new step, add 1 to this column and then delete the original column, but this is a bit messy and adds unnecessary steps.  And this is easy to fix by hacking the code using the formula bar.  This is what the UI produces (below).

So to make the change, adjust the formula in the formula bar as follows.  Remove the close bracket of Date.DayOfWeek (between [Date] and comma).  Then, after the comma following the [Date], add “Day.Monday)+1,”.  This will change the day numbers to start at 1, with Monday being the first day.

Working with Single Letter Names

When we finally load this calendar into Power BI, we will discover that any of our named columns, such as Month Name, will sort alphabetically by default.  This is the main reason to create the numbered columns, like FinMonth.  The FinMonth column can be used to override the natural sort order and present the month names in appropriate chronological order.  You can read more about this on Power BI Calendar Tables post.

Providing we are working with unique names this works fine, but if we need to load just the first character of a name, the normal sorting behavior will not work because there is no longer a 1:1 match between the text:number values.

i.e.
Months = J F M A M J J A S O N D
Days = M T W T F S S

To overcome this problem, we need to make sure each initial is unique, and this can be achieved by taking advantage of non-printable characters.

A non-printable character is a character that does not represent a visible symbol or number in text but is used for formatting or control purposes in computing. Examples include line breaks, tabs, and carriage returns, which affect how text is displayed or processed but do not appear in the printed output. These characters are essential for controlling the behavior of programs and formatting documents.

The HTML Unicode Character 8203, Zero Width Space, will enable us to make each name initial unique.

Start by extracting the 1st character from a named column.

This however will not create unique text values, so to overcome this challenge, update the formula with the following additional code.

& Text.Repeat(Character.FromNumber(8203), [Sorting Column])

Note: replace [Sorting Column] with the name of the column that contains your sort order.

The column will now sort correctly when the data is loaded into Power BI, and because we used the Zero Width Space character, there will be no visible impact on the presentation of the values when the initial column is used in a visual.

So at this point, this is the calendar I have built containing 10 columns in all.

YYMM Column

There are many other columns you will need over time, but today is about creating a basic calendar that gets us started, and there are still a couple of really important columns missing from our calendar.  The next column we need is a period identifier, that can be used for identifying each unique month.

This column was achieved by combining a few different transformation approaches, but given the length of this post already, I will just provide the final code.  You can start by adding a new custom step, and then paste this code into the formula bar.

= Table.AddColumn(#"Inserted D Day", "YYMM", each Text.End(Text.From([Date], "en-AU"), 2) & Text.PadStart( Text.BeforeDelimiter(Text.From([Date], "en-AU"), "/") , 2, "0"), Int64.Type)

I always start with a YYMM column because it is a number, but often I will further expand the calendar to include an MMMYY column as well, as the former will sort in the correct chronological order without the need of a sort column.  The YYMM and “human readable” MMMYY columns are great for visual filtering but are no good to use in solving time intelligence calculations.

ID Columns

The final column(s) we need to create a basic calendar table that will be sufficient for working effectively with DAX in Power BI, is the ever-important ID column.  The MonthID column simply provides a unique number for every YYMM in the calendar, and that in turn supports straight forward creation of custom time intelligence formulas.  Matt has covered time intelligence in detail here.  The YYMM column is not useful for time intelligence because the gap between each number is not evenly spaced, therefore movement around the calendar cannot be achieved with simple addition or subtraction.  eg 2511, 2512, 2601, 2602…

I therefore added an integer based ID column to identify each month over all time.  This new column starts at 1 and increases by 1 forever.

= Table.AddColumn(#"Inserted MMMYY", "MonthID", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date]))

Copy the Code to Reuse the Power Query calendar table

Now the calendar is done, it is just a matter of copying the code and saving it somewhere so it can be reused later.  I keep mine as a saved text file from Notepad and just copy and paste it when needed.  Just go to the Advanced Editor, copy the code and paste it somewhere for later.  Here is my code below.  Feel free to copy mine and use it in another Power Query Calendar Table if you like.

Financial Year Calendar

let
/*
Excelerator BI a division of Agile Insights 
** www.xbi.com.au 
**This Calendar was created and provided by Jason Cockington
**This can be freely shared as long as this text comment is retained.**
*/
#"Excelerator BI Fin Calendar" = "",
Source = List.Dates(StartDate, Length, #duration(1,0,0,0)),
StartDate = #date(2024, 1, 1),
Length = Duration.Days( EndDate - StartDate ) +1,
Today = Date.From( DateTimeZone.SwitchZone( DateTimeZone.UtcNow(), 10) ),
EndDate = Date.EndOfYear( Date.AddYears( Today, 0) ),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Inserted FinYear" = Table.AddColumn(#"Renamed Columns", "FinYear", each Date.Year([Date]+#duration(184,0,0,0)), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted FinYear", "FinMonth", each if Date.Month([Date]) >=7 then Date.Month([Date])-6 else Date.Month([Date])+6 , Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted MMM" = Table.AddColumn(#"Inserted Month Name", "MMM", each Text.Start([Month Name], 3), type text),
#"Inserted M Month" = Table.AddColumn(#"Inserted MMM", "M", each Text.Start([MMM], 1) & Text.Repeat(Character.FromNumber(8203), [FinMonth]), type text),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted M Month", "Day of Week", each Date.DayOfWeek([Date], Day.Monday)+1, Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted DDD" = Table.AddColumn(#"Inserted Day Name", "DDD", each Text.Start([Day Name], 3), type text),
#"Inserted D Day" = Table.AddColumn(#"Inserted DDD", "D", each Text.Start([DDD], 1) & Text.Repeat(Character.FromNumber(8203), [Day of Week]), type text),
#"Inserted FYMM" = Table.AddColumn(#"Inserted D Day", "FYMM", each Text.End(Text.From([FinYear], "en-AU"), 2) & Text.PadStart( Text.From( [FinMonth] ) , 2, "0"), Int64.Type),
#"Inserted MMMYY" = Table.AddColumn(#"Inserted FYMM", "MMMYY", each Text.Combine({[MMM], Text.End(Text.From([FinYear], "en-AU"), 2)}), type text),
#"Added MonthID" = Table.AddColumn(#"Inserted MMMYY", "MonthID", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date])),
#"Changed Type" = Table.TransformColumnTypes(#"Added MonthID",{{"Date", type date}, {"FinYear", Int64.Type}, {"FinMonth", Int64.Type}, {"Month Name", type text}, {"MMM", type text}, {"M", type text}, {"Day of Week", Int64.Type}, {"Day Name", type text}, {"DDD", type text}, {"D", type text}, {"FYMM", Int64.Type}, {"MMMYY", type text}, {"MonthID", Int64.Type}})
in
#"Changed Type"

Calendar Year Calendar

let
/*
Excelerator BI a division of Agile Insights 
** www.xbi.com.au 
**This Calendar was created and provided by Jason Cockington
**This can be freely shared as long as this text comment is retained.**
*/
#"Excelerator BI Basic Calendar" = "",
Source = List.Dates(StartDate, Length, #duration(1,0,0,0)),
StartDate = #date(2025, 1, 1),
Length = Duration.Days( EndDate - StartDate ) +1,
Today = Date.From( DateTimeZone.SwitchZone( DateTimeZone.UtcNow(), 10) ),
EndDate = Date.EndOfYear( Date.AddYears( Today, 0) ),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted MMM" = Table.AddColumn(#"Inserted Month Name", "MMM", each Text.Start([Month Name], 3), type text),
#"Inserted M Month" = Table.AddColumn(#"Inserted MMM", "M", each Text.Start([MMM], 1) & Text.Repeat(Character.FromNumber(8203), [Month]), type text),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted M Month", "Day of Week", each Date.DayOfWeek([Date], Day.Monday)+1, Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted DDD" = Table.AddColumn(#"Inserted Day Name", "DDD", each Text.Start([Day Name], 3), type text),
#"Inserted D Day" = Table.AddColumn(#"Inserted DDD", "D", each Text.Start([DDD], 1) & Text.Repeat(Character.FromNumber(8203), [Day of Week]), type text),
#"Inserted YYMM" = Table.AddColumn(#"Inserted D Day", "YYMM", each Text.End(Text.From([Date], "en-AU"), 2) & Text.PadStart( Text.BeforeDelimiter(Text.From([Date], "en-AU"), "/") , 2, "0"), Int64.Type),
#"Inserted MMMYY" = Table.AddColumn(#"Inserted YYMM", "MMMYY", each Text.Combine({[MMM], Text.End(Text.From([Date], "en-AU"), 2)}), type text),
#"Added MonthID" = Table.AddColumn(#"Inserted MMMYY", "MonthID", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date])),
#"Changed Type" = Table.TransformColumnTypes(#"Added MonthID",{{"Date", type date}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Month Name", type text}, {"MMM", type text}, {"M", type text}, {"Day of Week", Int64.Type}, {"Day Name", type text}, {"DDD", type text}, {"D", type text}, {"YYMM", Int64.Type}, {"MMMYY", type text}, {"MonthID", Int64.Type}})
in
#"Changed Type"

And here is the Power Query Calendar table PBIX workbook.

Learn to be a Power Query Expert

If you want to fast track your learning so you can leverage all the power of Power Query, take a look at our Power Query training course.

Once you know how, you will be able to save time when creating a Power Query calendar table and do fantastic things you never dreamed possible as a Power Query expert.