This page has 0 threads | Add post

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

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

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC