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

Sep 6, 2020 Zainab Balogun No Comment 666 Views

We’ve gone through Part 1, Part 2, and now here we are in the 3rd & final method on how to create a Calendar Table on Power BI using DAX. It’s been an interesting journey if I do say so myself.

Calendar Tables are so flexible that they have different methods of coming to life. Today’s method will be focused on using the DAX function CALENDAR. We’ve only used CALENDARAUTO so far so this is new territory.

The difference between both functions;

CALENDARAUTO: returns a table with one column of dates calculated from the model automatically.
CALENDAR: returns a table with one column of dates between ‘StartDate’ & ‘EndDate’.

Basically, for CALENDAR, you have control on how simple or bulky your Calendar Table would look because both start and end dates will have to be specified.

The CALENDAR function requires 2 arguments; StartDate & EndDate.

StartDate = 1^st day of the 1^st month (i.e. January) of the 1^st year included in the data

EndDate = 31^stof the last month (i.e. December) of the last year present in the data.

Looking for which year comes 1^st or last in the data manually will definitely be tedious. Luckily for us, there are a couple of DAX functions to help pull out that info. and we’ll be working with them soon. Just stay with me.

Before we begin, I want to introduce one of the functions we’ll be using; VAR (i.e. variable). Well, VAR is not really a function in the sense of function, it’s just a reserved name for defining named variables in DAX. Variables are used to store information for reference later. You’ll understand it’s use better as we go on.

So, as usual, to follow along, fire up your Power BI software & come with.

1. First things first, create an empty table and name it as ‘Calendar’.

create an empty table and name it as ‘Calendar’

There are a few things we need to establish first before using the CALENDAR function. The 1^st year, 1^st day & 1^st month. To derive the 1^st year, we need to find the 1^st day in the data, to get the 1^st day, we need to use the function MIN alongside any preferred date column in the data. This should typically be the date column with the earliest day required for analysis.

As a next step, type in the following formula;

Calendar = VAR firstday = MIN(Orders[Order Date])

Calendar = VAR firstday = MIN(Orders[Order Date])

SHIFT +ENTER

What we’ve just done is to use the MIN function to find out the 1^stday (i.e. the minimum date in the ‘Order Date’ column) and then we assigned it to the variable name ‘firstday’.

3. To find out the last day of the year, we’ll use the function MAX. Type in the formula;

VAR lastday = MAX(Orders[Order Date])

Calendar = VAR firstday = MIN(Orders[Order Date])
	   VAR lastday = MAX(Orders[Order Date])

SHIFT +ENTER

This has the same explanation as step 2. We used the MAX function to find out the last day of the year in the ‘Orders Date’ column and then assigned it to a variable name ‘lastday’.

Now that we have sorted out the days, next step is to figure out what year these days represent. To do that we’ll be making use of the YEAR function.

4. Type in the following formula;

VAR firstyear = YEAR(firstday)

Calendar = VAR firstday = MIN(Orders[Order Date])
	   VAR lastday = MAX(Orders[Order Date])
	   VAR firstyear = YEAR(firstday)

SHIFT + ENTER

What just happened? I’ll tell you what just happened. We just used the YEAR function to extract the year that the ‘firstday’ variable represents, we then assigned it to another variable ‘firstyear’ to keep for later use.

5. Applying the same method to find out the last year; type in the following formula;

VAR lastyear = YEAR(lastday)

Calendar = VAR firstday = MIN(Orders[Order Date])
	   VAR lastday = MAX(Orders[Order Date])
	   VAR firstyear = YEAR(firstday)
	   VAR lastyear = YEAR(lastday)

SHIFT + ENTER

Next step is preparing the ‘StartDate’ (i.e. Year, Month & Date) in one place before use in the CALENDAR function. To do this, we’ll make use of the DATE function.

The DATE function requires 3 arguments; Year, Month and Day. Year has already been sorted out & will be represented by the variable firstyear. Month by 1 (i.e.January) & Day by 1 (i.e. 1st day of the year)

6. So, type in the following formula to declare the variable ‘startdate’;

VAR startdate = DATE(firstyear, 1, 1)

Calendar = VAR firstday = MIN(Orders[Order Date])
	   VAR lastday = MAX(Orders[Order Date])
	   VAR firstyear = YEAR(firstday)
	   VAR lastyear = YEAR(lastday)
	   VAR startdate = DATE(firstyear, 1, 1)

SHIFT + ENTER

7. Applying the same logic to find ‘enddate’, type in the following formula;

VAR enddate = DATE(lastyear, 12, 31)

Where Year = lastyear, Month = 12 (i.e. December), Day = 31 (i.e. last day of December)

Calendar = VAR firstday = MIN(Orders[Order Date])
	   VAR lastday = MAX(Orders[Order Date])
	   VAR firstyear = YEAR(firstday)
	   VAR lastyear = YEAR(lastday)
	   VAR startdate = DATE(firstyear, 1, 1)
	   VAR enddate = DATE(lastyear, 12, 31)

And now, to finally pull everything together, we’ll be using the function RETURN alongside CALENDAR.

8. Type in the formula;

RETURN CALENDAR(startdate, enddate)

Calendar = VAR firstday = MIN(Orders[Order Date])
	   VAR lastday = MAX(Orders[Order Date])
	   VAR firstyear = YEAR(firstday)
	   VAR lastyear = YEAR(lastday)
	   VAR startdate = DATE(firstyear, 1, 1)
	   VAR enddate = DATE(lastyear, 12, 31)

	   RETURN CALENDAR(startdate, enddate)

Hit ENTER

9. Mark as date table;

You can decide to go ahead (or not if the Date column is enough) & add more columns to the Calendar Table i.e. Year, Month, Quarter, etc.

I want to assume that if you are here reading part 3 of this series, then you’ve gone through Part 1 & Part 2 because we learnt how to add columns to Calendar Tables (check Part 1). But for refreshers sake, I’ll add one column to the table & then you can go ahead and do the rest using the same format.

To create a Year column;

Navigate to the ‘Column tools’ tab and select ‘New column’. Follow the formula below and hit ENTER.

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

Phew!

I’m not even going to sugar coat it; THIS IS A LONG PROCESS. It’s not as straightforward as the other two and it might take a little bit of practicing to get used to it. But then I think the process is worth it if the purpose is met.

Although there’s a more straightforward formula that can be used instead of going through this long process;

Calendar = CALENDAR(DATE(YEAR(MIN(Orders[Order Date])), 1, 1),

DATE(YEAR(MAX(Orders[Order Date])), 12, 31)

Calendar = CALENDAR(DATE(YEAR(MIN(Orders[Order Date])), 1, 1),   DATE(YEAR(MAX(Orders[Order Date])), 12, 31)

i just thought to take the longer route because it makes the process easier to understand.

All in all, this method is best suitable for when;

Start and End dates are specific.
The date columns in the data is dated way too backwards, for example when there’s birth dates columns present, and CALENDARAUTO would pull out a bulky calendar instead, possibly starting from the 1800s depending on the oldest date of birth in the model

So, what do you think about this method? Would you go for it or would you stick to using CALENDARAUTO.

Let me know in the comment section.

Questions are also welcome.

On my next post, I’ll be explaining how to create the Calendar Table with Power Query. Do check it out.

P.S. This caps the last DAX solution on Calendar Tables i’ll be sharing. In the next post, i’ll jump to a Power Query Solution. Below are links to the DAX functions I have used from Part 1 up to this Part. You can check them out to have a better understanding of how the functions work:

CALENDAR

CALENDARAUTO

MIN

MAX

DATE

YEAR

FORMAT

MONTH

ADDCOLUMNS

VAR

Peace