01 Jun How to Create Power BI Calendar Table Using DAX Functions

This tutorial will demonstrate how we can create a calendar table in Power BI Report using the DAX(Data Analysis Expressions) function. Power BI is a Business Intelligence tool used for sharing insights in different companies. It is very efficient for creating reports and dashboards.

Dates are crucial in data; it helps us gain information about events, sales reports, and so many other useful references in our data. There are two different functions that can be used for creating a Power BI calendar. These are; the CALENDAR DAX function and CALENDARAUTO function.

CALENDAR Function

We can use the Calendar function to put a date range in data. Writing this function gives us a range of dates: the start date and finish date of what we set.

The Syntax

1 CALENDAR(<start_date>, <end_date>)
2 The start_date specifies the start date, while the end_date specifies the end of our date.

 

Let’s see an example below;

The date range we specified here should give us the date from May 1st, 2014 till January 1st, 2021.

CALENDAR (DATE (2014, 5, 1), DATE (2021, 1, 10))

 

CALENDARAUTO Function

CALENDARAUTO function generates the list of dates containing all the data model dates. The argument needed here is the fiscal year-end month.

The Syntax looks like this;

CALENDARAUTO([fiscal_year_end_month])

 

[fiscal_year_end_month]: This is a DAX expression that returns an integer from 1 to 12. When omitted, it defaults to the value specified in the calendar table template for the current user, if present; otherwise, it defaults to 12.

Let’s see the example below;

In this example, we click on New table and write the CALENDARAUTO function in the empty box. On writing that, Power BI will then create a start and end date calendar by looking at the date range of your table dates.

Now that we’ve examined the two different functions we can use, we can discuss how to add columns to the date table with DAX.

The ADDCOLUMNS Function

With the ADDCOLUMNS function, we can create new columns in our table.

The syntax looks like this;

ADDCOLUMNS ( <Table>, <Name>, <Expression> [, <Name>, <Expression> [, … ] ] )

 

Let’s look at this example below. This example shows us how we can create many columns. We created the Year column, the month_number, days of the week, Quarter, YearQuarter, and others. We use the Format function to specify how we want our date to be.

1 Calendar = 
2 
3 ADDCOLUMNS ( 
4 
5 CALENDAR (DATE(2014,5,1), DATE(2021,1,10)), 
6 
7 "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), 
8 
9 "Year", YEAR ( [Date] ), 
10 
11 "Monthnumber", FORMAT ( [Date], "MM" ), 
12 
13 "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), 
14 
15 "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), 
16 
17 "MonthNameShort", FORMAT ( [Date], "mmm" ), 
18 
19 "MonthNameLong", FORMAT ( [Date], "mmmm" ), 
20 
21 "DayOfWeekNumber", WEEKDAY ( [Date] ), 
22 
23 "DayOfWeek", FORMAT ( [Date], "dddd" ), 
24 
25 "DayOfWeekShort", FORMAT ( [Date], "ddd" ), 
26 
27 "Quarter", "Q" & FORMAT ( [Date], "Q" ), 
28 
29 "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) 
30 
31)

Our table should resemble what we have below;

And that’s a wrap. See you in our next post.