Build a Reusable Calendar Table with Power Query ⚡ - Excelerator BI

Build a Reusable Calendar Table with Power Query

I wrote a blog about Power Query calendar tables a few years ago over at PowerPivotPro.com.  I have learnt a lot about Power Query 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 new post showing you how to create a calendar table in power query.  Once you build a query like this, you can cut and paste the code into a text storage tool (like OneNote) 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

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 table in power query, I am going to use Power BI Desktop as the tool for this, but of course Power Query for Excel will work just as well and the process is identical.  In fact the calendar query at the end can easily be cut and pasted between Power BI and Power Query for Excel.

Step 1: Create a list of dates

To create a calendar table in power query,  I’m going to start off by creating a blank query. Get Data\Other\Blank Query.  This simply creates a new query with no data in it, and importantly no data source.

create a list of dates to create a calendar table in power query

I immediately renamed the query and gave it the better name, “Calendar”.

Next I created a list of dates to start my calendar off.  I just typed in 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 from the View menu.
  • 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.  At some stage in the future we are all hoping that Microsoft will deliver Intellisense to Power Query¹.  If that ever happens then this type of coding will become a lot easier.

¹ Note: M Intellisense was made generally available to Power Query in April 2019

create a calendar table in power query with M intellisense

The M language is self documenting.  As you can see 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.  I just entered some starting data into the parameters wizard as follows, then clicked the button to invoke the function.

image

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) Calendar has 1 line:

= List.Dates

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

= Calendar(#date(2016, 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 (inside the brackets) to the first query, which is the function itself.  This can be simplified by simply passing the parameters directly to the function itself.  The word Calendar (shown as 3 below) is therefore not a function, but a reference to the Calendar query that contains the name of the function (1 below).

image

I simply copied the code between the brackets (…….) from the Invoked Query function (2 above) and pasted this back directly into the source step of the Calendar query (1 above) as shown below.

calendar query in how to create a calendar table in power query

Then I just deleted the Invoked Function query – it is not needed.

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 given there is currently no Intellisense capabilities in Power Query.

After hacking this code to simplify it into a single query/single step, I then:

  • Converted the resulting “list” into a “table” using the “To Table” button.
  • Renamed the column to be called “Date”.
  • Changed the data type to Date.

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(2016, 1, 1), 100, #duration(1, 0, 0, 0))

Note that the start date is hard coded as 1/1/2016 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 current date.

Start Date

I added a new step using the “Add Step” button (1 below).  This created a new step (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.

image

I simply replaced this code (3 above) with the actual date (1/1/2016 in this case), and renamed the step StartDate. Note there is no equals sign used, just the date entered as 1/1/2016.  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 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 – I prefer to have everything embedded in the same master query).

image

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

image

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 legal in the M language – the steps do not have to be executed in any logical order.

End Date (Length of Calendar)

Just to make things difficult, you can’t specify an end date for the calendar.  Instead you have to specify how many periods (days in this case) the calendar should run for.  I want my calendar to run up to and including the current date every time the calendar is refreshed.  This implies I have to find out today’s date and then subtract the start date and hence calculate how many days are required in the calendar.

I added a new custom step again and entered the M function DateTime.LocalNow without any parameters.  (remember, if you just type the function name, the documentation will appear.

steps to create a calendar table in power query

From the documentation above, I can see that there are no parameters required for this function.  So I just added () 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.

image

The next problem is that this is Date/Time format.  I only want Date format, so I wrapped a DateTime.Date() function around the code to extract the date portion (shown below).  I renamed this step Today.  Each time the query is refreshed, this step will automatically update to reflect the current date.

image

Next I added a new step and subtracted StartDate from the Today to get the duration.

image

Just by looking at the UI above (red box), you can see that there is some strange data format returned.  You can probably work out this data is in the format DDD:HH:MM:SS.  This “duration” format wont work in the next step I am about to show you, so first I needed to extract an integer portion of the duration so it contains just the days.  I did this using 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.

how to create a calendar table in power query

Back 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.

image.

Great.  Now the user can manually change the StartDate step to any date, and the calendar will auto grow in length.  Actually this code will grow up to yesterday’s date.  You can simply add 1 to the Length to make it grow up to and including the current date.

Power Query Online Training

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.  I clicked on the last step (1 below) – “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”.

create a calendar table in power query

This is an easy fix.  Remember I said above that the steps don’t have to flow in order. I just added 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.

image

Add the Year

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

Learn to create a calendar table in power query

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 a buddy of mine Dave who is an Excel VBA consultant and guru.  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.

image

It seems 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.

image

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.

image

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.

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 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 Sunday 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).

image

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

Day of the week in create a calendar table in power query

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

image

Public Training Banner

YYMM Column

The next thing I did was to add a unique identifying column for each month.

YYMM Column in how to create a calendar table in power query

I simply added a custom column with the following formula to create this column.

=([Fin Year]-2000)*100 + [Fin Month]

I always prefer to create a column YYMM rather than MMYY as the former will sort in the correct chronological order without the need of a sort column.  This “human readable” column is great, but it is no good to use as in time intelligence calculations.

ID Columns

It is important to add an ID column that supports the creation of custom time intelligence formulas.  I cover time intelligence in detail here.  The YYMM column is not useful for time intelligence because the gap between each number is not evenly spaced.  eg 1711, 1712, 1801, 1802…

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.

(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 in Microsoft OneNote and just cut 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.

// Note there are 2 copies of the calendar table here, one for fin year, one for calendar year
//Fin Year July to June
let
    Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    StartDate = #date(2016, 1, 1),
    Today = DateTime.Date(DateTime.LocalNow()),
    Length = Duration.Days(Today - StartDate),
    Custom1 = #"Changed Type",
    #"Inserted Year" = Table.AddColumn(Custom1, "Fin Year", each Date.Year([Date]+#duration(184,0,0,0)), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Inserted Month" = Table.AddColumn(#"Inserted Day Name", "Fin Month", each if Date.Month([Date]) >=7 then Date.Month([Date])-6 else Date.Month([Date])+6  , Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Month", "Day of Week", each Date.DayOfWeek(([Date]), Day.Sunday)+1, Int64.Type),
    #"Inserted First Characters" = Table.AddColumn(#"Inserted Day of Week", "MMM", each Text.Start([Month Name], 3), type text),
    #"Inserted First Characters1" = Table.AddColumn(#"Inserted First Characters", "DDD", each Text.Start([Day Name], 3), type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Inserted First Characters1",{"Date", "Fin Year", "Month Name", "MMM", "Fin Month", "Day Name", "DDD", "Day of Week"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "FYMM", each ([Fin Year]-2000)*100 + [Fin Month]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"FYMM", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "MonthID", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"MonthID", Int64.Type}})
in
    #"Changed Type2"

//Calendar Year
let
    Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    StartDate = #date(2017, 1, 1),
    Today = Date.EndOfYear(DateTime.Date(DateTime.LocalNow())),
    Length = Duration.Days(Today - StartDate),
    Custom1 = #"Changed Type",
    #"Inserted Year" = Table.AddColumn(Custom1, "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Inserted Month" = Table.AddColumn(#"Inserted Day Name", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Month", "Day of Week", each Date.DayOfWeek(([Date]), Day.Sunday)+1, Int64.Type),
    #"Inserted First Characters" = Table.AddColumn(#"Inserted Day of Week", "MMM", each Text.Start([Month Name], 3), type text),
    #"Inserted First Characters1" = Table.AddColumn(#"Inserted First Characters", "DDD", each Text.Start([Day Name], 3), type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Inserted First Characters1",{"Date", "Year", "Month Name", "MMM", "Month", "Day Name", "DDD", "Day of Week"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "YYMM", each ([Year]-2000)*100 + [Month]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "MonthID", each (Date.Year([Date]) - Date.Year(StartDate))*12 + Date.Month([Date]))
in
    #"Added Custom1"

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 my and Ken Puls’s self-paced online Power Query training course.  You can also watch the free sample Power Query training videos here to get a feel about how you are going to learn.

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.

91 thoughts on “Build a Reusable Calendar Table with Power Query”

  1. Hi Matt! Just wanna say BIG THANKS for sharing this to us! You helped us save time updating static date lists and reports with no dynamic date references. Copy-pasting the link to this post in my M code so that future analysts can come back here for more details. Kudos and cheers from Japan!

  2. I have created a DatSource/Report that gets the last 12 Months of Data from TODAY, as such I want to create a Calendar Table that also has the last 12 Months of Dates from TODAY and is updated accordingly.

    If I create a Table using the following,
    tblDates = CALENDAR(NOW()-365, Now())

    The table is created as I require and updated on a daily basis , however, it does not appear in the Power Query Editor and cannot be referenced by other queries.

    Can I create this table using your code within this article so that the Start Date is Today – 365 days?
    How do I update the Table so that it will ALWAYS show the previous 365 days from TODAY?

    1. Correct. If you use DAX to create a calendar table using CALENDAR( ), it is not available in PQ. Yes, my code will work. You should be able to change lines 8, 9, 10 as

      StartDate = DateTime.Date(DateTime.LocalNow()),
      Today = DateTime.Date(DateTime.LocalNow()),
      Length = 365,

      I think it should work. It won’t handle leap years.

    2. Jason Cockington

      Hi Paul,
      If you need your calendar to adjust for leap years, rather then running the calendar for a length of 365 days, you’d be better off defining your Start Date as:
      StartDate = Date.AddYears( Date.From(DateTime.LocalNow()), -1)
      This will return Today, Last year, so when there are 29 days to February it will still work.
      Just an alternate option for you.

  3. Really an interesting post to create a calendar table using M-query.. My question is what happens in Leap years and in the month of Feburary, as well as months where the number of days are 31 when you are calculating month Number?
    For example
    FY Start Date : 01.10.2019 FY end date : 30.09.2020

    dates MonthName
    11/30/2019 February
    11/31/2019 March

    1. I’m not sure what you are asking. If you have a fin year that starts on a different date, you need to adapt the query accordingly. I show you how to do this for a 30 Jun fin year, just adapt that for your fin year.

  4. Vikrant Chidgopkar

    Hi Matt,
    Great post. If I have to subtract 365 days from my Enddate (which is today), how to change it?. Basically how to substract # of days?

    1. Jason Cockington

      Hi Vikrant,
      To subtract 365 days from your end date (Today), just simply modify your End Date formulawith another #duration modifier:
      = DateTime.Date(DateTime.LocalNow()-#duration(365,0,0,0))
      This will give you the same date as today, but from the previous year.

  5. Hie,
    Our Fiscal year begins on 01/01/2021 and ends on 31/12/2021.
    Our Accounting Preiod or Month are as follows
    Jan 01/01/2021 to 25/01/2021
    Feb 26/01/2021 to 25/02/2021
    Mar 26/02/2021 to 25/03/2021
    Apr 26/03/2021 to 25/04/2021
    May 26/04/2021 to 25/05/2021
    Jun 26/05/2021 to 25/06/2021
    Jul 26/06/2021 to 25/07/2021
    Aug 26/07/2021 to 25/08/2021
    Sep 26/08/2021 to 25/09/2021
    Oct 26/09/2021 to 25/10/2021
    Nov 26/10/2021 to 25/11/2021
    Dec 26/11/2021 to 31/12/2021
    When i filter by dates i want these months to be applied as they are. How do i go about it? Please assist.
    The same must apply on the time intelligent analysis.
    Thank you in advance.

  6. Arnau Martín Forné

    Awesome, thanks!
    One quick Q as a newbie…What part of the code I should change to make the calendar ending by december 2022?
    I tried simply to add 1640 days from start date (Duration.Days(1/12/2022 – StartDate)) and gave me this message

    Expression.Error: We cannot apply operator – to types Number and Date.
    Details:
    Operator=-
    Left=4,12133E-05
    Right=1/1/2019

    So lost…

  7. Dear Matt,
    Thank you for sharing your valuable insights and clear instructions with our community!
    I hope its ok that I had just 2 brief follow-up questions.

    1. I was wondering what steps you took to create the “ID Columns” with the syntax: (Date.Year([Date]) – Date.Year(StartDate))*12 + Date.Month(Date).

    2. I hope you don’t mind sharing the steps you took to create columns with only the first 3 letters showing of the month and week day showing

    I hope this is ok to ask. Apologies if it seems quite obvious as I just started on Power BI. I really found your article so helpful!

    Thank you so much in advance for all your time!
    -G

    1. 1. I used the Add Column, Custom Column and wrote the formula using a little M code and referencing the columns from the list on the right hand side of the dialog. It is very similar to adding a column to an Excel table.
      2. Using the menu, Add Column, Extract, First Characters, 3

      Good luck with the learning

  8. Hello Sir,

    How to actually calculate Fiscal Week in M-Language? Year Starts from 1st of May till 30th April.
    Where as Monday is the first Day of the Week and Sunday is the Last Day of the week.

    Any help?

    1. Both of the techniques needed to do what you want are covered above. Find where I add 184. Replace 184 with the number of days from the start of your fin year to the end of the calendar year. Also, find the point where I change the order of the days and make a suitable adjustment.

  9. Greetings From California’s Central Valley Matt,

    Everything worked as advertised in your excellent post. Did a tiny bit of tweeking to adjust my calendar to an October 1st Fiscal Year start, etc. and I am most pleased with the result. As a relative newcomer to the world of Power Query, Power Pivot, and Data Models, your post highlighted that I have a great deal yet to learn. This was a fantastic start. Have a great day 😉

  10. Matt,
    For the Start Date, how about adding a parameter lookup in Excel that updates without having to open the Mcode?
    So I added a new table in Excel and created this parameter

    let
    Source = Excel.CurrentWorkbook(){[Name=”Table2″]}[Content],
    #”Changed Type” = Table.TransformColumnTypes(Source,{{“StartDate”, type date}}),
    Crit = DateTime.Date(Record.Field(Source{0}, “StartDate”))
    in
    Crit

    Then in the Calendar, replaced the Start Date hard coded date with =Start which is the name of the Query above.

    Alan

    Alan

    1. Yes, if you are using Excel, that can work. The thing is the start date doesn’t change often, if ever. Personally I reserve those types of Excel parameters for things I need to easily change in the future.

  11. Hi Matt,

    Calendar week is Thursday to Wednesday, Calendar year starts from the week that contains the first date of Feb…

    How to incorporate this into the YEAR logic?

    Thanks a lot for help in advance

    Chau

    1. This is not a small topic that I can answer here. There is a week number option under “date” that you can use, or you can use some Index based on first date of your year and go from there.

  12. I believe there’s a slight mistake on the last column for MonthID.

    Shouldn’t the last added custom column, instead of:

    (Date.Year([Date]) – Date.Year(StartDate))*12 + Date.Month(StartDate)

    be:

    (Date.Year([Date]) – Date.Year(StartDate))*12 + Date.Month([Date])

    ? Please clarify.

  13. Dear Matt, thanks so much for this easy to follow tutorial on how to set up a calendar. This is amazing. I am going to read many more of your posts. Thanks again!

  14. Matt thanks so much for this awesome post. I knew I needed to do a calendar table but didn’t know where to start and I think I saw this post a long time ago before I was ready for it – you really set out a practical gem of a solution. Now I have to read all the comments which look really interesting.

  15. Narelle Guglielmi

    Hi Matt,
    Fin Year of April-March but that has the actual year as beginning in April is: = each Date.Year([Date]+#duration(275,0,0,0))-1, Int64.Type)
    eg. April 2014 is April 2014, March 2015 is March 2014

  16. Hey Matt,

    This is such a great tutorial to go through and I am nearly finished.
    However, I’ve hit a snag.
    My FY starts on April 1. So instead of 184, it’s 275 and to get the month it’s >=4 then Date.Month([Date])-3 else Date.Month([Date])+9.
    The problem is, I want that FY to be the year before.
    Example:
    If it’s 1 March 2020, I want the FY to be Mar19
    If it’s 1 April 2020, I want the FY to be Apr20

    Your code gives me:
    Mar20 as FY20
    and Apr20 as FY21
    I need:
    Mar20 as FY19
    Apr20 as FY20
    Mar21 would be FY20
    Apr21 would be FY21

  17. Dear Sir,
    I want to create a Calender table which starts with Year as 1st of May and ends on 30th April and weeks starts from monday to sunday, I need Days, weeks, Months, then seasons (Winter and Summer), The year is divided into two halves of 27 weeks and then seasonal month and their weeks.

    Kindly help

  18. Thank you so much…. you saved my day. I will write a longer comment of how I used your method to solve the “Data Model”.;. show items with no data… disabled.

    1. create a new query by referencing your fact table. click on the date column and then remove other columns. filter on the earliest date. Right click on the cell and drill down. This will give you the date. Name the query “FirstDate”. Then change the step in my query “start date” to =FirstDate. I will probably update this post some time to cover this.

  19. Hi Matt,

    Thanks for the wonderful web site you have, very helpful to newbies. By the way I have worked on the Calendar and have integrated to it two different fiscal years as follow:
    For July fiscal year one would use your query, for those who would have a fiscal year April 1 then they would have to change the following statement:
    From:
    #”Inserted Month” = Table.AddColumn(#”Inserted Day Name”, “Fin Month”, each if Date.Month([Date]) >=7 then Date.Month([Date])-6 else Date.Month([Date])+6 , Int64.Type)
    To:
    #”Inserted Month” = Table.AddColumn(#”Inserted Day Name”, “Fin Month”, each if Date.Month([Date]) >=4 then Date.Month([Date])-3 else Date.Month([Date])+9 , Int64.Type)

    This would adjust accordingly.

    For those who would be interested in having the solution to the fiscal year.

    Cheers!

    Chuck

  20. Hi Matt,

    RE: your fiscal year trick: “…add 184 days to the date before extracting the year…” This may work for calculating fiscal year but when it comes to smaller units of time this method comes undone. I created an additional column to derive all my fiscal columns from:

    #”Fiscal Source” = Table.AddColumn(#”Changed Type”, “Fiscal Source”, each Date.AddMonths([Date], 6))

    This way, the 29th, 30th, and 31st of August 2015, for example, all map to the 29th of February 2016, keeping the fiscal month correct (i.e. August is the 8th calendar month and all days in August should be in the 2nd fiscal month).

    I created columns for fiscal year, quarter, month, etc. so I assume calculating this once, as above, is more efficient than doing the calculation for every fiscal column. (I may be wrong – Microsoft could be optimising the additional recalculations away).

    I take offence at the great lengths you go to to remove the century from the year in your section on “YYMM Column”. To paraphrase Scott Morrison: Why the bloody hell would you? Keep it simple and stick to the ISO 8601 standard:

    #”Added Custom” = Table.AddColumn(#”Fiscal Source”, “Date ISO8601”, each Date.ToText([Date], “YYYY-MM-DD”))

    Adjust the format as necessary for the desired units of time. Speaking of which: the Microsoft documentation for Date.ToText says “For a complete list of supported formats, please refer to the Library specification document.” The library specification document is not publicly available – I don’t suppose you’d have access to this? I did leave feedback for Microsoft to include the list of formats in the documentation.

    Thanks,

    Michael

  21. Hi Matt,

    I have one minor correction. I believe that the code for adding the MonthID column in the body of the article is wrong:

    ([Fin Year] – Date.Year(StartDate))*12 + Date.Month(StartDate)

    The code in the complete listing works well:

    (Date.Year([Date]) – Date.Year(StartDate))*12 + Date.Month([Date])

    (Most people probably just copy the complete code but I was following the article step by step…)

    Thank you very much for this code!

    1. Hi Raf,
      Thanks for pointing that out. You are quite correct.
      At least we know now that everyone has just been copying the final code 😉

  22. Hi Matt and thanks for your excellent book – Supercharge Power BI. I downloaded the calendar above and notice it doesn’t have the ID column. I just added an index column to the table – will that work okay for the purposes of creating a unique daily ID?

    Also, I’m working on weekly reports. Is there much benefit creating a weekly ID column as well for each of our week endings or just use the daily ID column knowing we have 7 days per week ?

    1. You can add an index column, but technically you don’t need it as a date column can be used as the unique ID anyway. If you are doing weekly reports and you want to compare TY vs LY (eg week 10 this year vs week 10 last year), you can definitely benefit by a week ID column. If you just want to compare any given 7 days with the prior 7 days, or with the same set of dates from prior year, then you can just use the date column/date ID.

  23. Hi Matt,

    Just looking at your calculation for the “Fin Year”
    = Table.AddColumn(Custom1, “Fin Year”, each Date.Year([Date]+#duration(184,0,0,0)),Int64.Type)
    does this calculation account leap years?

    Thanks

    Andy

    1. The first time I saw this +184 formula I had my doubts too. But it does work. The reason it works for leap years is because the +184 gets added to the second half of the calendar year which doesn’t include any leap year differences

  24. Hi Matt, This has been great as I also require the Aust Financial year calendar. I have been looking at the calendar though and given that it is based off the Datetime.localnow() It only seems to go up to the current date. Is there any way to have the end date of the calendar be in a year from today? Thanks for your help.

  25. Hi Matt,

    This was excellent and really simple to follow, thanks. I am a beginner and this made a lot of sense

    I created the YYMM column as explained. you mentioned that this will allow sorting chronologically but when I am in report view and want to sort MMM column by YYMM I get an error message:

    ‘We can’t sort the ‘MMM’ by ‘YYMM’ There can’t be more than one value in ‘YYMM’ for the same value in ‘MMM’. I fixed the issue by sorting by Fin Month but I’d like to understand how to use YYMM. can you please let me know?

    Thanks a lot!

    1. The column YYMM will sort correctly without using a sort over-ride. a column MMYY will not sort correctly unless you have a sort over-ride column. You can only use a sort over-ride column if it is at the same level of carnality as the destination column you are overriding . eg, if you are sorting a MMM column, you will need a sort over-ride column with 12 unique values that match to each value in the MMM column

  26. Nice post, but I’m wondering what’s the performance of generating a table in PowerQuery versus having a(n excessively) large static date table (filtered with Now()) as a query. Basically I’m wondering if PowerQuery would generate the data faster or read from disk faster (let’s assume an SSD).

    Any inkling of what the performance differences might be?

    1. Sorry, I don’t know. I assume loading from SSD would be faster than using PQ to generate the table.If you have the calendar table already, then I would never use PQ. If you don’t have the calendar table, I think PQ is fast enough to do the job – it is unlikely that your fact table will be finished first, so the PQ calendar table will be waiting for the fact table anyway.

    1. Ive tried to create new column .Butstill I am getting this error saying ;

      Expression.Error: The column ‘YYYYMM’ of the table wasn’t found. Details: YYYYMM

      “Inserted MonthYearLong” = Table.AddColumn(#”Inserted First Characters1″, “Year Month”, each [Month] & “-” & Text.From([Year])), But I am getting error while I am trying to record the column details;

      #”Reordered Columns” = Table.ReorderColumns(#”Inserted First Characters1″,{“Date”, “Fin Year”, “Month Name”, “MMM”, “YYYYMM”, “Fin Month”, “Day Name”, “DDD”, “Day of Week”}),

  27. Hi Matt, I was following the steps you described very closely, but somehow, the “Day of Week” I generated is as followed Mon = 1, Tues = 2, Wed = 3, Thu = 4, Fri = 5, Sat = 6 and Sun = 0. So by adding 1 to the formulae will not work. How can I change Sun =7??

      1. I have not edited to the formula yet, and the result column is as I described above. The unedited formula is as follows: = Table.AddColumn(#”Renamed Columns2″, “Day of Week”, each Date.DayOfWeek([Date]), Int64.Type).

        1. The default is Sunday being the first day of the week. Hence Sunday is 0, Monday is 1, etc. There is an optional parameter where you can specify an alternate first day of week Date.DayOfWeek([Date],Date.Monday)

  28. Abhinav Sharma

    hi there,
    i have created the calendar from my table date filed, please help me to select the date range as financial year wise because with this it provide result as calendar year wise.

  29. The fiscal year formula didn’t work for me. I started with the date 01/04/2017 and wanted this to show 2018 as the fiscal year (i.e. 2017/18) so the only i could get that to work was to add 365. It seemed to work until I got to 01/01/2018 and then made this 2019 (but it should really be 2018 as its part of fiscal year 2017/18. I’m probably doing something wrong but I copied your steps to the letter (great tips though btw, I learned a lot in just one small tutorial)

    1. My fiscal year formula works for a 30 june year end. If that is not your year end, then of course it won’t work. I add 184 days because there are 184 days from July to Dec. if your FY starts on 1st april, I guess you should add 275 instead

      1. Yes you are right. I put 91 days as being from the start of the calendar year and should have taken that from 365 instead! D’oh

  30. Cara Jones-Readle

    Hi Matt
    I have tried to use this same logic to create a calendar that grows by weeks; i.e. 26/11/18 to 03/12/18. But the calendar has grown by 102 weeks due to the Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2018,7,1)) portion of the formula. Is there a way to change this so that my calendar will only show the number of weeks that have occurred since the 26th of Nov 2018?
    Thanks
    Cara

    1. If I were creating a week calendar, I would first take my day calendar above, then filter on the last day of your week (eg if Sunday is the last day of each week, then filter on Sunday). Then remove all day related columns (such as day name) and then remove duplicate rows

  31. Hi,
    thanks for your post, I found it quite useful and interesting. I’ve used DAX calendars before, and I can see the benefits of a Query M Calendar table.

    As our firm has a different Financial Year from the one mentioned in the post (the new year starts on 1 April), you have to subtract days from the date to get the FY, and in this scenario you have to calculate with the leap years (29 February). So I made a solution for it, I hope somebody will find it useful.

    #”Added Custom” = Table.AddColumn(#”Inserted Day of Year”, “Custom”, each if Date.IsLeapYear([Date]) = true then Date.Year([Date]-#duration(91, 0, 0, 0)) else Date.Year([Date]-#duration(90, 0, 0, 0)))

    Regards,
    Andris

  32. You will need to write a new query that first extracts the public holidays. I guess that would be a table with the date in 1 column and the name of the holiday in the second column. Set this table so it doesn’t load. Then from the calendar table, merge the calendar table with the holiday table using the date as the common column. Set it to left outer join and then extract the columns from the holiday table.

  33. Hi, thanks for the post. How should I adjust the code if I wan to add to calendar also information about public holidays as separate column. Information about public holidays is in separate table. I want to do it in Query not DAX.

  34. I don’t know what I liked the most about the article:
    :: – Calendar tips
    :: – Power Query M hacks and tips

    Now you made me curious of what else can we leverage from M. Please give me your impressions on “Collect, Combine, and Transform Data Using Power Query in Excel and Power BI” By Gil Raviv, because I’m buying another book and this can be the one.

    Thanks for the article.

  35. There are various ways to do this. One easy way to get a month calendar is to create a day calendar, add a “Day number of month” column, then filter to keep the first day of each month only.

  36. Hi, my dates are showing up as days whereas yours are 1 record per month (1/1/2016, 2/1/2016 etc). How did you achieve this?

    Thanks

  37. I am using the september 2018 version of PBID and I did not get the parameter prompt after entering List.Dates any idea why this feature would have been removed?

  38. Howdy Matt – there is a line break in your code sample above which causes errors on “MMM” – you can see it on the left “, each Text.St….
    if I remove the line break it works perfectly.
    Thank you,

  39. Hello Matt,

    Thanks for the example.

    My company uses a custom fiscal calendar consisting of 13 periods per year. Each period contains 4 weeks, except for “leap years” when period 13 contains 5 weeks.

    We already have a date table that contains all this info. I’ve been struggling with getting time intelligence in PowerBI to play nice with our calendar. Do you think your approach would help?

    Thanks,

    Mark

  40. Peter Rodionoff

    I was still using the code from your previous article on powerpivotpro.com!

    Thanks for this – it looks awesome.

  41. Jonathan Cowperthwaite

    is it correct to assume that if you were working in PowerBI and wanted a calendar table you should use the appropriate DAX function? If however you need to create a calendar table and you don’t have DAX then this is a good solution? I.e. is there a reason you might use this method, even if you did have access to the DAX formulas?

    1. Whether to use DAX or Power Query is a personal choice. Marco Russo has produced a very comprehensive dax calendar table. Personally I prefer Power Query. I prefer to leave dax to Data modelling and power Query/ M for Data loading and transformation, but it is not clear cut. However a valid use case may be to load public holidays into your calendar in the form of a working day flag. This would be a lot easier and neater in Power Query in my view.

      1. Hi Matt,
        Thanks for sharing this knowledge. I am a frequent visitor to yours and other blogs.
        I have tweaked this a little so my end date will be the EoM of previous month.
        With regard to workdays I currently just maintain an excel sheet a table with dates and one with holidays and use NETWORKDAYS. I then merge in PQ with the calendar table on the first of the month. I would love PQ to have this function. Soon I expect to have to maintain calendar with workdays across Asian countries.

  42. There’s a typo “MMM#(lf)” in the step #”Reordered Columns”.

    Furthermore, your MonthID column does not return the right sequence. I replaced it by:

    #”Added Custom1″ = Table.AddColumn(#”Changed Type1″, “MonthID”, each (Date.Year([Date]) – Date.Year(StartDate))*12 + Date.Month([Date]))

    Nice to know the +#duration(184,0,0,0) trick!

      1. Hi Matt, adding 184 days to a date delivers the wrong result in some cases, e.g. when the date is 29 to 30-Dec, so the financial month will be wrong. I find that looking at the current month number and deriving the financial month from that works a lot better: = Table.AddColumn(#”Inserted Month Name”, “Fin Month”, each if Date.Month([Date])> 6 then Date.Month([Date]) – 6 else Date.Month([Date]) + 6)

        1. It works for financial year. If I used it for financial month (which I see I did), then that was definitely a (lazy) mistake on my behalf – I didn’t think it through. Thanks for letting me know. I have modified the code

  43. Hi Matt:

    TX for the tutorial. A very interesting point you raised is that the steps do not need to follow in any order.

    Your quote above reads:
    “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 legal in the M language – the steps do not have to be executed in any logical order.”

    Does this apply to scalar values only? In other words, PQ can pick up on a scalar value anywhere in the Applied Steps? As I understand it, the function steps need to be in a specific order as the query transforms the data from top to bottom, not so?

    please confirm this point.

    Cheers

    1. There is no requirement for the steps to be in any order regardless if they are scalar values or tables. By default the UI will create the steps in order because that is the logical way people will want to work through a task, but this is not a requirement for it to work. You can put any line of code in any order you like and it will still work.

  44. I do similar, but more often than not, I make the calendar fully dynamic based on the data being pulled. First I create to variables. ONe is the StartDate, and the other is the EndDate.
    Start Date might be: #date(Date.Year(List.Min(#”Removed Other Columns”[Due Date])),1,1)
    So it pulls Jan 1 from the earliest year in the data set. Could be invoice records, Ledger data, whatever. I am programmed to always do full years when I build tables. Not sure if it was your book that discussed that or not to make the DAX functions that depended on dates work best in financial models.

    End Date would be: #date(Date.Year(List.Max(#”Removed Other Columns”[Due Date])),12,31)
    So Dec 31 from the latest year in the data set.

    Then my calendar starts with the source as =StartDate and my DatesAsList looks like this:
    = List.Dates(Source,Number.From(EndDate)-Number.From(Source)+1,#duration(1,0,0,0))

    Next step is convert to a table, and add all the relevant columns.

    1. Awesome, that is exactly what I am thinking to do, but I do not understand pretty well what you did it, could you give more details, please

  45. Great post Matt, your method is so easy and the financial year DAX formula is a great way to achieve that column as opposed to other methods I have seen…Say thanks to Dave for us! and keep up the great blog posts!

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top