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:

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:

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:

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.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

ALL BLOGS

POWER BI BLOGS

POWER BI BLOGS

Creating a Power BI date table (calendar table)

Creating a date calendar

Adding columns to a calendar

Setting custom quarters

Head office

London

Manchester