Power BI Date Dimension Demystified Part 2

Hello All,

Let us continue with our journey of demystifying the Date Table. In my last post, we learned to use CALENDARAUTO() function. We will continue today with the second DAX function called Calendar(). We will also look at how to extract other “relevant columns” such as year, month name, month number, etc. I will go over the following in this post

  1. Create a date column using the CALENDAR() function.
  2. Use multiple DAX formulas to extract/add these columns.
  3. Use one DAX formula to generate the entire Date Table that will include all these columns.
  4. Dynamic dates selection and usage.

CALENDAR() Function:

The CALENDAR() functions take two parameters, StartDate, and End Date, and returns a table with one column with all dates between the StartDate and EndDate.

For this exercise, let StartDate be 01/01/2020 and EndDate be 12/31/2020.

If you were following this series from Part 1 then your environment is already set and just follow along. If you are new and want to follow along with same dataset then please go through Part 1 and the Power Query

Let us begin by navigating to the Modeling tab, click on the New Table, and use the following code to create a column in this table, containing all the dates between the start and end date as shown below.

date table using calendar = CALENDAR(DATE(2020,01,01), DATE(2020,12,31))

Once you hit enter, you should have the Date Column in your model. To view it, click on the data tab on the left and you should be a single column in this table of Date/Time datatype ranging from 1/1/2020 to 12/31/2020 as shown below.

As you can see we have a very similar result to what we had in Part 1. Since in Part 1 we used CALENDARAUTO(), Power BI fetched the minimum (start-date) and maximum (end-date) automatically from our model. However, for the CALENDAR() function, we need to specify the StartDate and EndDate.

Once we have this column, we can then extract important and relevant information regarding the date. We will be extracting information such as Month Number, Year, Month Name, Weekday, Quarter, Second approach to Quarter, Week Number, Day Number of the Month, WeekDay Number of the week.

I have included all these code snippets as follows so you can follow along with me as I do this. All we need to do is after the Date Column is created (using the approach above), create a new column for each of the relevant details.

To create the Month Number: 

  1. Click on the Data tab on the left on the Power BI Desktop.
  2. Once you are in the Data tab, you should see the date table that we created above on the right under fields. 
  3. If you followed along then you should have a table named “date table using calendar“.
  4. Click on the new column icon.
  5. Enter the following formula in the formula bar. 
  6. Month Number = Month(‘date table using calendar'[Date])
  7. Press enter.
  8. Your new column named Month Number is ready.
  9. Follow the same process for Month Name, Year, Weekday, Quarter, Quarter Second Approach, Week Number, Day Number, Weekday Number.

Use the following code snippets to generate these columns. I have included the images of the next two columns as an example. 

We have already done the first and second below. 

  • date table using calendar = CALENDAR(DATE(2020,01,01), DATE(2020,12,31))
  • Month Number = MONTH(‘date table using calendar'[Date])
  • Month Name = FORMAT(‘date table using calendar'[Date], “mmmm”)
  • Year = YEAR(‘date table using calendar'[Date])
  • Weekday = FORMAT(‘date table using calendar'[Date], “dddd”)
  • Quarter = Var monthnum = MONTH(‘date table using calendar'[Date])

Return 

SWITCH

(

                    TRUE(),

                    monthnum <=3, “Q1”,

                    monthnum > 3 && monthnum <= 6, “Q2”,

                    monthnum > 6 && monthnum <= 9, “Q3”,

                    monthnum > 9, “Q4”

)

  • Quarter Second Approach = 

// storing the month number in a variable called mnth

var mnth = MONTH(‘date table using calendar'[Date])

// getting the quarters 

var myqrt = ((mnth -1)/3 ) + 1

// truncate this result using Trunc function and WITHOUT passing the optional params

var trnc = TRUNC(myqrt)

// concat the alpahbet Q to this result 

var addQ = “Q ” & trnc

// Retun this

return addQ

  • Weeknumber = WEEKNUM(‘date table using calendar'[Date])
  • Day Number of the month = DAY(‘date table using calendar'[Date])
  • Weekday number of the week = WEEKDAY(‘date table using calendar'[Date])

You should have the following columns in your date table by now.

Let us take a pause here and create these columns using the Part 1 approach. In that post, we used Calendarauto() function. Using the same approach let us create other columns. 

  1. Once again navigate to the Data tab. 
  2. Click the table named date table from calendarauto. 
  3. Click on the new column and enter the code snippets 

These are the code snippets for the CALENDARAUTO() function. Please note that the start date and end date will be different for both these functions. The reason being in CALENDAR() we are specifying the StartDate and EndDate, whereas in CALENDATAUTO() it identifies the start date and end date based on the model. We have covered the CALENDARAUTO() in detail in the previous post here.

// all the columns for the Date Table from the start

  • date table from calendarauto DAX = CALENDARAUTO()
  • Month Number = MONTH(‘date table from calendarauto DAX'[Date])
  • Month Name = FORMAT(‘date table from calendarauto DAX'[Date], “mmmm”)
  • Year = YEAR(‘date table from calendarauto DAX'[Date])
  • Weekday = FORMAT(‘date table from calendarauto DAX'[Date], “dddd”)
  • Quarter = Var monthnum = MONTH(‘date table from calendarauto DAX'[Date])

Return 

SWITCH

(

                    TRUE(),

                    monthnum <=3, “Q1”,

                    monthnum > 3 && monthnum <= 6, “Q2”,

                    monthnum > 6 && monthnum <= 9, “Q3”,

                    monthnum > 9, “Q4”

)

  • Quarter Second Approach = 

// storing the month number in a variable called mnth

var mnth = MONTH(‘date table from calendarauto DAX'[Date])

// getting the quarters 

var myqrt = ((mnth -1)/3 ) + 1

// truncate this result using Trunc function and WITHOUT passing the optional params

var trnc = TRUNC(myqrt)

// concat the alpahbet Q to this result 

var addQ = “Q ” & trnc

// Retun this

return addQ

  • Weeknumber = WEEKNUM(‘date table from calendarauto DAX'[Date])
  • Day Number of the month = DAY(‘date table from calendarauto DAX'[Date])
  • Weekday number of the week = WEEKDAY(‘date table from calendarauto DAX'[Date])

Awesome !!! we now have a date table with relevant data using both Calendar and Calendarauto approach. Let us dig a little deep into these DAX formulas that we have written. If you notice, we created multiple calculated columns based on the very first date column. This is okay to do so. However, we can combine all these separate DAX formulas into one.

For this let us create an entire date table using a single DAX formula and adding these columns within it. 

  1. Click on the modeling tab.
  2. Click on the new table icon.
  3. Paste the following code snippet.
  4. Once you press enter, your entire date table will be ready and populated. 

Date Using DaxCal Calendar =

// This variable is storing the column values

VAR DaxCal =

   CALENDAR ( DATE ( 2020, 01, 01 ), DATE ( 2020, 12, 31 ) )

RETURN

   // AddColumns returns a table with new columns specified by the DAX expressions

   ADDCOLUMNS (

     DaxCal,

     “Year”, YEAR ( [Date] ),

     “Month Number”, MONTH ( [Date] ),

     “Month Name”, FORMAT ( [Date], “mmmm” ),

     “Quarter”,

       “Q “

         & TRUNC ( ( ( MONTH ( [Date] ) – 1 ) / 3 ) + 1 ),

     “WeekNum”, WEEKNUM ( [Date] ),

     “Day of the Month”, DAY ( [Date] ),

     “Weekday Number of the Week”, WEEKDAY ( [Date] ),

     “Weekday Name”, FORMAT ( [Date], “dddd” ),

     “Full Date Only”, FORMAT ( [Date], “mm/dd/yyyy” )

   )

You should have a date table as shown below.

Let us break down the code.

  1. We are calling Calendar Function just like we did before. However this time we are storing the result in a Variable.
  2. We then want to return a table with other columns created using the DAX expressions.
  3. To do so we are calling the ADDCOLUMNS function.
  4. The first parameter we need to pass is the name of the table. In this case, it is our variable. I called it DaxCal.
  5. The next parameter is the name of the column. I called it Year.
  6. The last parameter is the DAX expression. In this case, it is YEAR([DATE]).
  7. Follow the same process till all the other columns have been called.

I have included the very similar code snippet for CALENDATAUTO() function that was used in Part 1

Date Using DaxCal Calendarauto = 

VAR DaxCal =

   CALENDARAUTO ()

RETURN

   ADDCOLUMNS (

     DaxCal,

     “Year”, YEAR ( [Date] ),

     “Month Number”, MONTH ( [Date] ),

     “Month Name”, FORMAT ( [Date], “mmmm” ),

     “Quarter”,

       “Q “

         & TRUNC ( ( ( MONTH ( [Date] ) – 1 ) / 3 ) + 1 ),

     “WeekNum”, WEEKNUM ( [Date] ),

     “Day of the Month”, DAY ( [Date] ),

     “Weekday Number of the Week”, WEEKDAY ( [Date] ),

     “Weekday Name”, FORMAT ( [Date], “dddd” ),

     “Full Date Only”, FORMAT ( [Date], “mm/dd/yyyy” )

   )

Though we have created a Date Table, we are not done yet. Let us improve on this approach. What we just did is what I call the brute force method of creating a Data Table using the Calendar function. What I mean by this is that we hardcoded the StartDate and EndDate values in this.

Let us take this further and make it dynamic. If you remember from my previous post, There are 3 Main Things we need. StartDate, EndDate, and Duration. Once again I will reiterate that please remember this as this will help us understand the concept of Date Table when we create the Date Dimension using M Query in the future posts.

By now we know that the function CALENDAR() requires StartDate and EndDate. For now, we handed it by providing static values. Let us make it more dynamic.

Let us say, that we needed our date table to start from the first Order date as the StartDate and the last ship date as the EndDate.

To do so we will declare two more variables as follows.

Date Using DaxCal Calendar Dynamic =
// we are getting the minimum of the Order Date and storing it in a variable
Var MinDate = MIN(OrdersData[Order Date])

// we are getting the latest Ship Date and storing it in a variable
Var MaxDate = MAX(OrdersData[Ship Date])

// Calling the Calendar function and passing into it, the two variables as the arguments and returning the result with relevant columns
VAR DaxCal =
CALENDAR(MinDate,MaxDate)
RETURN
ADDCOLUMNS (
DaxCal,
“Year”, YEAR ( [Date] ),
“Month Number”, MONTH ( [Date] ),
“Month Name”, FORMAT ( [Date], “mmmm” ),
“Quarter”,
“Q “
& TRUNC ( ( ( MONTH ( [Date] ) – 1 ) / 3 ) + 1 ),
“WeekNum”, WEEKNUM ( [Date] ),
“Day of the Month”, DAY ( [Date] ),
“Weekday Number of the Week”, WEEKDAY ( [Date] ),
“Weekday Name”, FORMAT ( [Date], “dddd” ),
“Full Date Only”, FORMAT ( [Date], “mm/dd/yyyy” )
)

With this we were able to use two DAX functions, CALENDAR() and CALENDARAUTO(), to create the date table with additional columns as needed. I hope that I was able to shed some light on these two DAX functions. In the file below you will find all the code snippets that we used thus far.

All the columns for the Date Table using CALENDAR()

// all the columns for the Date Table from the start -- CALENDAR()


date table using calendar = CALENDAR(DATE(2020,01,01), DATE(2020,12,31))

Month Number = MONTH('date table using calendar'[Date])

Month Name = FORMAT('date table using calendar'[Date], "mmmm")

Year = YEAR('date table using calendar'[Date])

Weekday = FORMAT('date table using calendar'[Date], "dddd")


Quarter = 
            Var monthnum = MONTH('date table using calendar'[Date])
            Return 
                SWITCH
				(
				
                    TRUE(),
                    monthnum <=3, "Q1",
                    monthnum > 3 && monthnum <= 6, "Q2",
                    monthnum > 6 && monthnum <= 9, "Q3",
                    monthnum > 9, "Q4"
                )


Quarter Second Approach = 

// storing the month number in a variable called mnth
var mnth = MONTH('date table using calendar'[Date])

// getting the quarters 
var myqrt = ((mnth -1)/3 ) + 1

// truncate this result using Trunc function and WITHOUT passing the optional params
var trnc = TRUNC(myqrt)

// concat the alpahbet Q to this result 
var addQ = "Q " & trnc

// Retun this
return addQ



Weeknumber = WEEKNUM('date table using calendar'[Date])

Day Number of the month = DAY('date table using calendar'[Date])

Weekday number of the week = WEEKDAY('date table using calendar'[Date])

All the columns for the Date Table using CALENDARAUTO()


// all the columns for the Date Table from the start -- CALENDARAUTO()


date table from calendarauto DAX = CALENDARAUTO()

Month Number = MONTH('date table from calendarauto DAX'[Date])

Month Name = FORMAT('date table from calendarauto DAX'[Date], "mmmm")

Year = YEAR('date table from calendarauto DAX'[Date])

Weekday = FORMAT('date table from calendarauto DAX'[Date], "dddd")

Quarter = 
            Var monthnum = MONTH('date table from calendarauto DAX'[Date])
            Return 
                SWITCH
				(
                    TRUE(),
                    monthnum <=3, "Q1",
                    monthnum > 3 && monthnum <= 6, "Q2",
                    monthnum > 6 && monthnum <= 9, "Q3",
                    monthnum > 9, "Q4"
                )


Quarter Second Approach = 

// storing the month number in a variable called mnth
var mnth = MONTH('date table from calendarauto DAX'[Date]
)

// getting the quarters 
var myqrt = ((mnth -1)/3 ) + 1

// truncate this result using Trunc function and WITHOUT passing the optional params
var trnc = TRUNC(myqrt)

// concat the alpahbet Q to this result 
var addQ = "Q " & trnc

// Retun this
return addQ


Weeknumber = WEEKNUM('date table from calendarauto DAX'[Date])

Day Number of the month = DAY('date table from calendarauto DAX'[Date])

Weekday number of the week = WEEKDAY('date table from calendarauto DAX'[Date])

In the next blog post, we will create Date Dimension using Power Query. If you have questions/queries/comments please do not hesitate to reach out to me.

Till next time !!!!

Published by Ansh Purohit

I am a data enthusiast and enjoy working with data manipulation tools and languages. Power BI is my passion and I consider myself a life long learner. I enjoy sharing my knowledge with others because I firmly believe that Knowledge Increases by Sharing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: