Creating a calendar or date table in Power BI using built-in calendar functions
Learn how to create a basic date table in Power BI, using either the CALENDAR or CALENDARAUTO functions, and also learn how to then add custom columns of your own.

Posted by Sam Lowrie on 17 May 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating a Power BI date table (calendar table)

Date tables are useful for most data models, and are compulsory for using time intelligence functions:

Power BI Date Calendar

An example of a basic date calendar with different granularities.

Creating a date calendar

Assuming you haven't created one in another programme, choose New Table from the Modelling tab:

Power BI Date Table

New Table assumes that you will use a function to create a table (such as a date table, or a summary table using aggregates).

There are two main functions for creating the date table. CALENDARAUTO()  extrapolates to the start of the first financial year in the model and to the end of the last:

Power BI Date Calendar

The optional argument FiscalYearEndMonth can change which month is the last in your financial year. The default is 12, meaning that December is the end of the year.

This creates a list of all the dates in yoru model, which should look like this:

Power BI List

I'd recommend naming the new column to anything but Date since this is a command word. Try Date Key or Reference Date.

 

The drawback of this function is that it doesn't contain any future dates, which could hinder calculations including forecasting. As an alternative, you could use the CALENDAR() function:

Power BI Calendar Date Table

Indicate the earliest date and latest date you will need and Power BI will create a list of all the dates in between. Using the month name avoids the risk of American dates.

Adding columns to a calendar

Now to add all the columns that we might need into the table. First up, add the names of the weekdays:

Power BI Weekday Date Calendar

FORMAT can be used (as for Excel custom dates) to extract parts of the date. In this case DDDD returns the weekday (DDD would return the shortened weekday).

While there are functions for YEAR, MONTH, DAY and others, it isn't necessary to use them. Add a new column equal to the Date Key:

Power BI Date Table

When referencing a date column a drop down appears with the available parts to extract, such as the Date from a date/time column or MonthNo to show 1 instead of January.

Repeat this action for all the options available apart from Quarter, to get something like this:

Power BI Date Parts

You can now reference these columns in calculated columns, measures, and in visuals (including slicers).

Setting custom quarters

Missing from the above list is the quarter for each given date, due to there being a problem with the value returned:

Power BI Quarter Date Calendar

The Quarter is based by default on a year that starts with January, but what happens if your year starts in April?

Rather than use a complicated SWITCH function, a quicker way to proceed is to create the column using this code:

Power BI Date Calendar Quarter

Interestingly despite giving the start of the year within CalendarAuto, no calculations take this into account by default.

To create more advanced date calendars check out this blog, or for custom date periods this one.

This blog has 0 threads Add post