Hello, welcome to part 2 of the series on Creating Calendar Tables in Power BI
Last time I was here, I mentioned there are different ways of creating a custom calendar tables using DAX.
In the last post, I focused on one method using only CALENDARAUTO (if you haven’t gone through it yet, you might want to check it out before going ahead with this to provide more clarity as we move along).
The method I am discussing here, adds a new function; ADDCOLUMNS to the one we already know; CALENDARAUTO.
ADDCOLUMNS function literally makes it possible to add columns to the formula as we build our table. Columns like Month, Year, Quarter, etc.
This makes it easy to create the Calendar Table all at once without having to ‘create new column’ at every step. This might seem confusing right now but I assure you that it’s not difficult to get a hang of. Just stay with me.
For this exercise, we’ll be generating a Calendar Table with 4 columns; Month, Year, Quarter and Month Number
So, fire up your Power BI software and let us begin…
1) Navigate to Data view and click ‘New Table’ under the Home tab
Figure 1: create new table
2) Rename newly created table ‘Calendar’ and type in the function ADDCOLUMNS
Figure 2: Insert ADDCOLUMNS
ADDCOLUMNS has three (3) arguments; Table, Name and Expression.
- The Table argument: is where the CALENDARAUTO function stands in and it only appears once in the formula.
- The Name argument is the name you intend to give your column and it has unlimited appearance. i.e. you can have as many names as you want depending on how many columns to be present in the Calendar Table.
- Expression = formula to be used to generate the column e.g. FORMAT[Date, “mmmm”]. It also has an unlimited appearance in the ADDCOLUMNS function so it’s use depends on how many columns you intend to create.
3) In the parenthesis that follows ADDCOLUMNS, type in the 1st argument which is the function CALENDARAUTO and close parenthesis.
NOTE: DO NOT hit the ENTER button just yet. We are only allowed to hit ENTER until we are done writing the whole formula. To enter another line, hit SHIFT + ENTER.
Figure 3: insert CALENDARAUTO()
Calendar = ADDCOLUMNS(CALENDARAUTO(),
Recall: what CALENDARAUTO does is to return a table with one column of dates calculated from the model automatically.
Now it’s time to input the 2nd argument; Name1. As mentioned earlier, the name to be given depends on the column to be built.
4) Starting with Month, type in “Month”
Figure 4: insert “Month”
Next is the 3rd argument; Expression.
5) The expression to generate the column for month is what goes in here and that is:
FORMAT([Date], “mmmm”)
Figure 5: insert expression for Month
Calendar = ADDCOLUMNS(CALENDARAUTO(),
"Month", FORMAT([Date],"mmmm"),Hit SHIFT + ENTER to enter a new line
Next column we create is ‘Year’ and for that;
6) Name of the column = “Year”
7) Expression = FORMAT([Date], “yyyy”)
Figure 6: insert name & expression for Year
Calendar = ADDCOLUMNS(CALENDARAUTO(),
"Month", FORMAT([Date],"mmmm"),
"Year", FORMAT([Date], "yyyy"),SHIFT + ENTER
Next column in our calendar table; QUARTER
8) Name = “Quarter”
9) Expression = FORMAT([Date]), “QQ”)
Figure 7: insert name & expression for Quarter
Calendar = ADDCOLUMNS(CALENDARAUTO(),
"Month", FORMAT([Date],"mmmm"),
"Year", FORMAT([Date], "yyyy"),
"Quarter", FORMAT([Date], "QQ"),SHIFT + ENTER
Last column; MONTH NUMBER
10) Name = “Month Number”
11) Expression = MONTH([Date])
And finally, hit ENTER
Figure 8: insert name & expression for Month Number
Calendar = ADDCOLUMNS(CALENDARAUTO(),
"Month", FORMAT([Date],"mmmm"),
"Year", FORMAT([Date], "yyyy"),
"Quarter", FORMAT([Date], "QQ"),
"Month Number", MONTH([Date]))And that’s it! We are basically done building our Custom Calendar Table and it only took writing a couple of formulas at a go.
What’s left is to;
a) Mark the newly created table as the designated Date table
Figure 9: mark as date table
b) Create a relationship between the table and another date table in the data model
Figure 10: create relationship
Once all these steps have been carried out, the calendar table is set and ready for use.
Now, let’s talk about which one is a better practice. This or the first?
👍
- Creating a calendar table using ADDCOLUMNS seems faster because it only involves noting down the columns to be used and then generating them using their appropriate expressions.
- The formula is reusable i.e. after generating the Calendar Table, the formula can be copied somewhere for later use in another data.
👎
- It can be a tad bit confusing when using it for the first time, especially for a beginner.
But then at the end of the day, it depends on you. It depends on which method you prefer. And you can only be sure of that after trying out both methods.
So, play with both and pick what suits you best.
On my next post, I’ll be explaining how to create the Calendar Table with DAX function.
Thanks for reading,
Peace.
