This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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 15 May 2019

In this blog

Creating a date calendar
Adding columns to a calendar
Setting custom quarters

Creating a Power BI date table (calendar table)

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

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:

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:

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:

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:

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:

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:

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: