by Matt Allington

In this post, I am going to show you how simple it is to create a custom calendar using Power Query.  If you follow the steps below, you will get a good sense of how capable Power Query is, and may spark your interest to learn more about it.

Different Types of Calendars

There are lots of different ways of creating a custom calendar for use in your Power Pivot workbooks, and plenty of discussion about them on PowerPivotPro.com (25 prior to this one).  Historically I have just used an Excel workbook that I created with all the relevant columns, and enough rows of dates to cover for the next year.  There is one problem with this approach however –  the date over runs when you are looking at last years’ data in a pivot.

over run

As you can see above, we have dates into the future that are showing sales for last year with no sales this year – because we haven’t had “this year” yet.  It is a pain to change the import filter on your Excel calendar each time you refresh (not practicable actually).  You can write some snappy Excel functions in your source calendar table to code around this, or you could make your DAX more complex, but better still – I think this is a great opportunity to learn some new skills – Power Query.

Building a custom calendar from scratch in Power Query is actually quite straight forward, and I am going to take you through it step by step below.

First Create a New Blank Power Query Workbook.

The Blank Query option is right at the bottom of the “From Other Sources” menu.

blank query

If you haven’t done so already, turn on your Formula bar from the view menu.  You will need this so you can easily see the Power Query Code that is generated by the UI tools, and also to allow you to edit the code by hand when needed.

turn on formula

Create a list of dates

The first step is to use the Power Query Language in the formula bar to create a list of dates.  Note that the Power Query Language is case sensitive so you must capitalise correctly.

Type in the code      =List.Dates

and then press the “Invoke” Button.  Note how Power Query gives you some help about the function you just typed in.

list.dates

Enter a start date, the number (count) of days you want and the granularity of the calendar (step 1 means daily), and press OK.  For now we will just select 100 days.

enter dates

But we don’t actually want 100 days, we want a “dynamic end date”.  We need to change this new line of code to create the dynamic end date.

edit code

Simply replace the 100 in the formula with this Power Query code substitute

Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2015,1,1))

You just need to make sure the date used above matches the first date in your list.  As I am sure you can work out, this code uses a function Duration.Days to count how many days there are between today’s date and the start date you entered.  There are other ways to do this, including this one described by Dominik, any approach is fine.

If you want your calendar to have a 1 day lag (ie when you refresh it today, you want all dates up until yesterday’s date, then simply subtract 1 from the formula above like this.  Note the minus 1 is outside of the brackets.

Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) – #date(2015,1,1)) -1

Convert the list to a table

Simply click the button on the screen.  Just accept the default options on the screen prompt.

ctt

Now add an Index column

There is a button for this too.  The Power Query team has thought of everything!

add index

The Index column starts at 0 by default (see 1 below).  I like my index to start at 1, but it is easy to change.  You can either click on the cog icon next to the Added Index step (see 2) or you can simply change it in the formula bar (see 3).

mod index

Add Calendar Metadata

Now it’s time to add your calendar metadata, like days, months, quarters etc.  I first renamed my Column1 to “Date” and then inserted a new column

add column

The steps are simple to add a Year column

  1. Give it a name
  2. Select your Date column
  3. Insert it into the editor window
  4. wrap the column name with the function =Date.Year(    )

add year

Add all the other columns

You can add as many columns as you need in your table.  I used the above approach to add the following columns

Column Power Query Formula to use
Month Number =Date.Month( )
Day =Date.Day(  )
Day Name =Date.ToText([Date],”ddd”)
Month Name =Date.ToText([Date],”MMM”)
Quarter Number =Date.QuarterOfYear( )

So the Calendar Table is now starting to look pretty good.

wip

Create a short year column

To do this, I just duplicated the Year column, converted it to text and then split the column so that I only ended up with the last 2 characters.

short year

I then created a nice little Quarter/Year column using the “Add Custom Column Button” again and some more Power Query Language.  =Number.ToText([Short Year]) & “Q”& Number.ToText([Quarter Number],”00”)

I’m just essentially concatenating the Short Year, the Quarter Number with some formatting text added in for good measure.

qy

A bit of tidy up, move some columns and I ended up with this.  A great Calendar that automatically updates to today’s date every time you refresh your workbook.

Final Calendar

The Code

If you don’t want to go through all these steps yourself, you can just copy and paste my code from here.

let
Source = List.Dates,
#"Invoked FunctionSource" = Source(#date(2015, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - #date(2015,1,1)), #duration(1, 0, 0, 0)),
#"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Table from List", "Index", 1, 1),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Number", each Date.Month([Date])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day", each Date.Day([Date])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Day Name", each Date.ToText([Date],"ddd")),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Month Name", each Date.ToText([Date],"MMM")),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"Date", "Index", "Year", "Month Number", "Month Name", "Day", "Day Name"}),
#"Added Custom5" = Table.AddColumn(#"Reordered Columns", "Quarter Number", each Date.QuarterOfYear([Date])),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom5", "Year", "Copy of Year"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Year", "Short Year"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Short Year", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type","Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Short Year.1"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Short Year.2", "Short Year"}}),
#"Added Custom6" = Table.AddColumn(#"Renamed Columns2", "Quarter Year", each Number.ToText([Short Year]) & "Q" & Number.ToText([Quarter Number],"00")),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom6",{"Index", "Date", "Day", "Day Name", "Month Number", "Month Name", "Quarter Number", "Quarter Year", "Short Year", "Year"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Date", type date}, {"Day", Int64.Type}, {"Index", Int64.Type}, {"Month Number", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Name", type text}, {"Quarter Year", type text}, {"Year", Int64.Type}})
in
#"Changed Type2"

All you need to do is copy it from above, then go to Power Query, click on View/Advanced Editor and paste the text into the window

paste

Where to get more information?

I recommend M is for Data Monkey by Ken Puls and Miguel Escobar. I have written a review about this book here.  There are also some great reference materials on the Internet for Power Query.  I keep a list of the good ones I find on my links page.

My next project is to work through the challenges of a 4/4/5 calendar table.  I would love to hear from anyone that has already attempted this using Power Query.
Edit March 2015:  Here is a link to the next post covering the 445 calendar.

Matt Allington is a is a professional Power Pivot consultant and trainer based in Sydney Australia.

  Subscribe to PowerPivotPro!
X

Subscribe

Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia. 

This Post Has 52 Comments

  1. Matt — when I try and play along and I substitute above code snippet for the 100, it breaks — looking for Token Comma expected (highlighting the dash)…

    1. Thanks for alerting me to the problem. If you cut and paste the substitute code now – it will work.

      The issue was that I put a ‘space’ either side of the minus sign in the blog to make it easier to read. I tested it in the Power Query to make sure that the space didn’t affect the result, and it didn’t. What I didn’t realise was that when I added the space either side of the minus sign in my blog editing tool, the minus sign =CHAR(45) somehow got replaced with something that looks like a minus sign CHAR(150)

      I have edited the code above and it now works. Thanks again

  2. Matt – I enjoyed this article and I have a interest in developing a 4-5-4 calendar. I would be willing to help you work on the development of your 4-5-4 calendar. I work in the retail industry in a reporting capacity and may be able to help provide some insights.

    1. Thanks Micheal. If you have any repeatable logic to calculate the first week of the year, that would be of interest. From experience, there are lots of different approaches, which is probably why 445 is not a standard feature – because there is no standard.

      1. Matt, I am sending you my spreadsheet that I put together for my company’s calendar. It follows the calendar of the NFR (National Federation of Retailers). I will email it to you at the address you gave to Leonard.

          1. Thanks for trying. I have had a horror couple of days with my ISP with many things not working, including this email address. Sorry about that.

            Just changed my host and it is now working

  3. I have a 445 calendar that I built in regular Excel using formulas (including leap weeks). I’d be happy to pass that along to you so you can see the logic behind the calculations and see if you can translate that to PQ. No promises as to quality!

    My question: could you not also take a date table in a database table (or Excel sheet) and filter in PQ to only bring in dates up to and including yesterday via PQ? When it comes to calendars that have infinite unique configurations, that might be easier than coding the calendar dynamically.

  4. Hi Matt,
    We use a Fiscal Year Calendar that runs from July-June. In Excel, I have a formula that inserts a Fiscal period column, 1 being July, 2 August and so on. Is there a power query formula that will do this?

    Rita

    1. Hi Rita. It seems that the Date.Month formula does not handle financial years. One way to do what you want is to use an if statement.

      So add a custom column, then put the following formula

      =if[MonthNum] <=6 then [MonthNum] +6 else [MonthNum] -6 (assumes you have already added a month number column called "MonthNum". You can delete this calendar version of the month number after you have added the fin year version

  5. Hi Matt,
    Is there an easy way to create a column such as MonthID which assigns a unique Month No to each entry in Calendar Table … e.g. For Jan 2014 dates MonthID = 1; Feb 2014 MonthID = 2; … Jan 2015 MonthID = 13

    Thanks

    Ted

    1. How about modifying the line that inserts the month number (or take a copy of the column it if you want to keep the regular month number column).

      So we need a way to track which year we are in because the 1st year you add nothing (just keep the month number), second year you add 12 to the month number, 3rd year you add 24 etc).

      So by adding (Date.Year([Date])-2013)*12 just before the last bracket, it will add 0 x 12 in the first year, 1 x 12 in the second year, and so on.

      = Table.AddColumn(InsertedCustom1, “MonthNum”, each Date.Month([Date]) + (Date.Year([Date])-2013)*12 )

      Just be sure to start on 1 Jan, and make sure the date above (2013 in my case) is a match to the first year in your calendar.

      I will probably follow up with a further post on turning this into a function at some time, which will make reuse much easier.

  6. My company has a fiscal calendar that begins the first full week of February and runs for 13 “periods” which are 27 days long. I’m struggling with creating a calendar that accommodates our Fiscal Quarters and Fiscal Periods. Any advice??

  7. Hi Matt,
    thank you very much for sharing – that List.Dates was new to me – very useful!

    I see that you used quite a couple of steps to create the “Short Year”.
    With “Text.End(Text.From([Year]),2)” it can be done I one step.

  8. This is a great idea! Thanks. Any idea why I cannot Mark as Date Table? I’ve tried this a couple of times, and the Mark as Date Table is disabled. That needs to be done to take advantage of the time intelligence, right?

    1. You need to mark as date table to use I built time intelligence functions. You need a date column of one of the types “date”, it probably needs contiguous dates too. Switch to data view, click in the date column and try then.

  9. How would you write a formula in M to compare a column vs a hardcoded date? Easy excel formula, “IF([@Date]<DATE(2014,1,1),1,0)" but having trouble translating that to M.

      1. Ah! Beautiful. Now to just translate the rest of the actual formula! haha

        =IF(IF(DATE(2014,4,1)[@[Sales End Date]],0,ROUND(((DATE(2014,4,1)-[@[Sales Start Date]])/30.42),0))))=0,0,IF((IF(DATE(2014,4,1)[@[Sales End Date]],0,ROUND(((DATE(2014,4,1)-[@[Sales Start Date]])/30.42),0))))>21),1,VLOOKUP((IF(DATE(2014,4,1)[@[Sales End Date]],0,ROUND(((DATE(2014,4,1)-[@[Sales Start Date]])/30.42),0))))),RAMP,2,FALSE)))

        Thanks, Matt.

  10. Dear Matt,
    if I want to use this approach to generate 2 date tables in my data model, can I somehow generate 2 copies of the date table from one query?

    Or would I have to use 2 (duplicate) queries?
    If so, does this slow performance? Or is Power Query smart enough to tell that the queries are identical?

    Thanks for your support!
    Sebastian

    1. You will need to use 2 duplicate queries. This will double the calculation time but it is really fast anyway – it won’t be an issue. Rather than create 2 identical queries, I suggest you create 1 query and load it up into PP. Then create a second query with reference to the first query and load that up. By doing it this way you end up with only 1 query that you will have to maintain in the case that something changes in the future

  11. Interesting article! I am trying to use just PowerBI Desktop for this, and when I try to use the CALENDAR() function, PowerBI doesn’t recognize it. Is this feature only available in Excel 2016?

  12. Thanks for this blog, it has been so helpful that I bought the ebook from Amazon last week and the 2nd edition paperback from B&N last night.

    I’m stumped and need help. I using PowerBI and I need to create a measure for PY Sales that takes into consideration Leap Year and gives me the proper subtotals for partial months. My formula works great for full months but not for partial months. I’m getting full-month PY sales in the subtotal. Here’s my DAX, how do I fix it?

    PY Sales =
    SUMX ( VALUES ( ‘Calendar'[YearMonthNumber] ),
    IF (CALCULATE ( COUNTROWS ( VALUES ( ‘Calendar'[Date] ) ) )= CALCULATE ( VALUES ( ‘Calendar'[MonthDays] ) ),
    CALCULATE ([Net Sales], ALL ( ‘Calendar’ ), FILTER (ALL ( ‘Calendar'[YearMonthNumber] ),
    ‘Calendar'[YearMonthNumber]= EARLIER ( ‘Calendar'[YearMonthNumber] ) – 12)),
    CALCULATE ([Net Sales],ALL ( ‘Calendar’ ),CALCULATETABLE ( VALUES ( ‘Calendar'[Calendar_Day] ) ),
    FILTER (ALL (‘Calendar'[YearMonthNumber]),’Calendar'[YearMonthNumber]= EARLIER ( ‘Calendar'[YearMonthNumber] ) – 12))))

  13. Just in case someone here wants to tackle the question. Here are a few details of my data model:

    DAILYSALES fact table linked to the Calendar table via the Date field
    Net Sales = SUM(DAILYSALES[Sales-Net])
    YearMonthNumber = (‘Calendar'[FY_Number] – MIN( ‘Calendar'[FY_Number] )) * 12 + ‘Calendar'[FY_MonthNumber]
    MonthDays = COUNTROWS ( FILTER( ‘Calendar’, ‘Calendar'[YearMonthNumber] = EARLIER ( ‘Calendar'[YearMonthNumber] ) ) )
    DayNumber = Day([Date])

    If there’s an easier way to do any of this please let me know that too.

  14. @Matt, How would I add a WeekNumber column and a year-week? In excel or as a calculated column in PowerPivot, I would use =WEEKNUM([Date], 2). I would also add year week by using =[Year]&”-“&FORMAT([WeekNumber], “00”). I do not know enough about M to translate that. Year Month would also be helpful.

    1. Duplicate the date column, then click on this new column and select Transform\Date\Week\Week of Year. If these week numbers don’t meet your business needs, you could divide the ID column by 7, extract the remainder and build your own week of year number that way. But it would depend on your business rules. To build a YYWW number, I would then multiply the short year number by 100 and add the week number.

  15. Is there a way to add specific times to the source column? So that there are rows created for 1/1/2015 09:00 AM, 1/1/2015 10:00 AM etc.

    1. Start with DateTime.FromText(“2016-03-30T09:00:00”) Then use List.DateTimes(Source, 365 ,#duration(0,1,0,0)) worked.

  16. Hi Matt, thanks for the blog, calendar table works great.

    I have one question though. Is it possible to extend the last date of the calendar table??
    I’m working on some forecasts for my database and I need the calendar table to reach the year 2020.
    Help?

    Thanks and Regards

    1. You will note in my code that I started with a date (1 Jan 15) and then added 100 days. Later I changed the 100 days for a dynamic end date. In your case, simply work out how many total days you need to get to 2020 and add that number instead of 100, then stop there – no need to make a dynamic end date.

  17. Can’t seem to get the ‘whole number’ 7 to become 07 (via the Add Custom Column) using this formula

    =Number.ToText([MonthNumberOfYear],”00″)

    Created column has 7 still as 7 (not 07)?

  18. Hi Matt, …I’m working thru the 2nd Edition of the Power Pivot and Power BI book

    I’m at the portion where it discusses creating the “New Table” (chap. 18 – Multiple Data Tables)
    …and I thought I’d put my ‘limited’ Power Query skills to the test and Query the “BookData.accdb” file (to create Lookup table)

    Here’s the Script from the Editor (to make a long story/message …short):

    _DimDate = Source{[Schema=””,Item=”DimDate”]}[Data],
    #”Removed Columns” = Table.RemoveColumns(_DimDate,{“DateKey”, “FullDateAlternateKey”, “DayNumberOfWeek”, “EnglishDayNameOfWeek”, “SpanishDayNameOfWeek”, “FrenchDayNameOfWeek”, “DayNumberOfMonth”, “DayNumberOfYear”, “WeekNumberOfYear”, “EnglishMonthName”, “SpanishMonthName”, “FrenchMonthName”, “CalendarQuarter”, “CalendarSemester”, “FiscalQuarter”, “FiscalYear”, “FiscalSemester”}),
    #”Added Custom” = Table.AddColumn(#”Removed Columns”, “TestYEARmonth”, each Number.ToText([CalendarYear]) & Number.ToText([MonthNumberOfYear],”00″)),
    #”Removed Duplicates” = Table.Distinct(#”Added Custom”, {“TestYEARmonth”}),
    #”Renamed Columns” = Table.RenameColumns(#”Removed Duplicates”,{{“MonthNumberOfYear”, “TestMonth”}, {“CalendarYear”, “TestYear”}, {“TestYEARmonth”, “CalTestYEARmonth”}}),
    #”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“CalTestYEARmonth”, Int64.Type}})
    in
    #”Changed Type”

    When all is said/scripted and done, my CalTestYEARmonth column has 20037 (instead of 200307)
    …so I’m not able to create the relationships described in the book?

  19. Worked like a charm

    #”Added Custom” = Table.AddColumn(#”Removed Columns”, “TestYEARmonth”, each ([CalendarYear] * 100) + [MonthNumberOfYear]),

    Just realized that by pushing the ‘refresh’ button on my browser, I’m able to see your response faster as well 🙂

    Thank you.

  20. Hello, I did all the steps and connected the Calendar Table to my model, but it only shows two columns: Index and Short Year! Why is that, am I missing something? I’m using Excel 2010 and PowerPivot and PowerQuery as Add-Ins. Great tutorial! Thanks.

    1. I have seen this before in Excel 2010. Try setting the number formats in Power Query first, I’ve remove the “any” data types. You will need to completely remove the table from Power Pivot first

Leave a Comment or Question