A Step By Step Guide To Creating A Custom Calendar Table In Power BI (Part 1)

First, why do you need a Calendar Table, also popularly referred to as Date Tables?

All reports include dates; MONTHLY, YEARLY, QUARTERLY, etc. and nearly all data sets have date fields. To make it easier to work with date and time related analysis, having a separate table used specifically for calendar dates therefore is important to have. The single date column on this Calendar Table can be linked with all other date fields in our data models.

In Power BI, there are two (2) types of date dimensions;

• Default
• Custom

The default date dimension, like I call it here, is active by default. Evidence of which can be verified through this route:

In the Power BI Desktop, File Menu -> Option and Settings -> Options in the Options Window, under Current File, Data Load; Time Intelligence: Auto Date/Time

This method when checked, automatically creates a hidden date table for each field in the model that has a Date or Date/Time data type.

Although, the default date table method is easier to create, it’s use is not encouraged because it’s not flexible enough for complex analysis, relationships, creating hierarchies or marking as a date tables.

3 ways to use Custom Calendar Tables are:

1. Import from an external application/data base
2. Create with Power Query M Language
3. Create with DAX formulas

That said, the focus here will be on creating a simple Custom Calendar Table using DAX. So fire up your Power BI software and let’s go.

To create a Calendar Table using this method, we will need a DAX function that returns a list of dates. And there are 2 of such functions.

• CALENDAR
• CALENDARAUTO

We will be using CALENDARAUTO here because this automatically generates a list of dates based on the available dates in our data model. On the other hand, for CALENDAR, the start date and end date must be specified.

What CALENDARAUTO does is to check for the first and last year in your data model, then it creates a list of dates from 1 January of the first year, to 31 December of the last year.

Where you don’t run a January to December Calendar, such as in Financial Years, the function has an optional argument for Year End Month where you can specify for example, 03 to indicate March. Then, the list of dates would be from 1 April to 31 March.

Please note that to use CALENDARAUTO, your model should have at least one table that contains at least one date column. Otherwise, the function won’t work.

So, let’s begin…

Navigate to Data view and click ‘New Table’ under the Home tab

1. Once that’s done, rename the table to ‘Calendar’ and type in the function CALENDARAUTO() then hit Enter.

Create first column; Dates

     
The function CALENDARAUTO() scans the data model, looks for the first and last year, then generates a single column of dates starting from the 1st January of the 1st year to 31st December of the last year in the data

NB: CALENDARAUTO() has one argument; [FiscalYearEndMonth] which can be ignored unless the fiscal year in the data does not follow the regular calendar year (i.e. does not begin in January or ends in December)

Once this table with a single column has been created, it can and must now be marked as the designated calendar table. To do this;

2. Select the Calendar table, navigate to the ‘Table tools’ tab and click on the drop down arrow ‘Mark as date table’, Mark as date table, then select ‘Dates’ as the date column.

We now have our explicit Calendar Table.

NOTE: This (marking as date table) is done to prevent Power BI from creating two different date tables (i.e. default and custom) which not only causes confusion but also uses extra memory.

3. Now we need to add extra columns like Year, Month, Quarter, etc. to expand our table and enable extra reporting dimensions.

Starting with Year, navigate to the ‘Column tools’ tab and select ‘New column’. Follow the formula below and hit enter.

Year = FORMAT(‘Calendar’[Date], “yyyy”)

4. Next; Month. Navigate to the ‘Column tools’ tab and select ‘New column’ as done previously. Follow the formula below and hit enter

Month = FORMAT(‘Calendar’[Date], “mmmm”)

Create MONTH column

5. For Quarter, create a new column and type in the formula as shown below and of course, hit Enter.

Quarter = FORMAT(‘Calendar’[Date], “QQ”)

Our Calendar table is coming to life.

No, we are not done yet, we still need to;

a) Make sure there’s a relationship between the newly created table and the date fields we have in our data model.

b) Ensure that the months appear in the right order during visualization and not alphabetically.

To do these respectively,

a) Navigate to the ‘Model view’ and create a relationship by connecting the Calendar table to a date column in any of the available tables in the model.

For the 2nd, we need to create an additional column ‘Month Number’ which will return numbers from 1-12 to represent corresponding months. To do this,

b) Navigate to Data view, select the Calendar table, create a new column, follow the formula below and hit enter;

Month Number = MONTH(‘Calendar’[Date])

Create MONTH NUMBER column

Almost there…

Lastly, select the column ‘Month’, navigate to the ‘Column tools’ tab, click on the drop down ‘Sort by column’ and select ‘Month Number’

Sort by Month Number

After this is done, you can be rest assured that the months will be sorted in the right order i.e. from Jan. – Dec. and not alphabetically.

We are now done with creating a Custom Calendar Table in Power BI using CALENDERAUTO. Now you can go ahead and write DAX formulas, create visuals, etc. with our newly created Calendar Table.

We will be visiting the 2nd method using DAX (i.e. Part 2) in our next post. Watch out.

For now, try your hands on this method and enjoy how easy it is. I hope you have learned a thing or two.

Peace.