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 on power bi

Figure 1: create new table

 

2) Rename newly created table ‘Calendar’ and type in the function ADDCOLUMNS

Figure 2: Insert ADDCOLUMNS function

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()

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

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

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

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

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

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

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

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.

Hey there! Thanks for stopping by. I'm Zainab Balogun, a lover of big data, a part-time writer and artist & a full time bookworm. Check out my blog on Medium & My LinkedIn Profile

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*