DAX Date Table: Adding Time Intelligence Columns

1. Enriching the Date Table with Calculated Columns

We will be adding these new columns as calculated columns to the Date table you created. As a quick refresher from an earlier lesson, a calculated column is created using a DAX formula and its values are calculated for each row, then stored in the data model. This is perfect for static attributes of a date, like the year or month it belongs to.

We'll add each column one by one. This approach makes it very clear what each DAX function does.

For a great overview of the functions we'll be using, let's turn to a video from WiseOwlTutorials. We will watch it in segments as we build our table.

Your Turn: Add Year and Quarter Columns

Now, let's apply this to your Power BI file.

1. In Power BI, navigate to the Data view and select your Date table.
2. From the Table tools ribbon, click New column.
3. In the formula bar, enter the following DAX expression and press Enter:

   Year = YEAR('Date'[Date])
   

4. Create another new column for the quarter. For better readability in our reports, we'll format it with a "Q" prefix. Use this formula:

   Quarter = "Q" & QUARTER('Date'[Date])
   

   The & operator in DAX concatenates (joins) the text "Q" with the number returned by the QUARTER function.

2. Handling Months and Weekdays: The Sorting Challenge

When adding columns for month names (e.g., "January") or weekday names (e.g., "Monday"), we face a common challenge: sorting. By default, Power BI will sort text columns alphabetically, which would result in "April, August, December..." instead of the correct chronological order.

To solve this, the best practice is to create two columns:

• One for the name (for display in visuals).
• One for the number (for sorting purposes).

Let's see how this is done.

The FORMAT function is incredibly versatile. By providing the format string "MMMM", we get the full month name. Using "MMM" would give us the abbreviated name (e.g., "Jan").

Your Turn: Add Month and Day of Week Columns

Let's add the necessary columns for both months and weekdays.

1. Month Number: Create a new column with the formula:

   Month Number = MONTH('Date'[Date])
   

2. Month Name: Create another new column using FORMAT:

   Month Name = FORMAT('Date'[Date], "MMMM")
   

3. Day of Week Number: We'll use the WEEKDAY function. This function has an optional second argument that defines the start of the week. We'll use 2 to specify that Monday is 1 and Sunday is 7, a common business convention. Create the new column:

   Day of Week Number = WEEKDAY('Date'[Date], 2)
   

4. Day of Week Name: Finally, use the FORMAT function again. The string "dddd" gives the full day name. Create the new column:

   Day of Week = FORMAT('Date'[Date], "dddd")
   

You should now have a Date table with several new, useful columns.

3. The Crucial Step: Sorting by Column

You've created the columns, but your Month Name and Day of Week columns will still sort alphabetically in visuals. Now we'll fix that using the Sort by Column feature.

This feature tells Power BI to use the order of a different column (our numeric column) to sort the text column.

Your Turn: Apply the Correct Sorting

1. In the Data view, select the Month Name column in your Date table.
2. The Column tools ribbon will appear. Click Sort by column and choose Month Number from the dropdown.
3. Now, select the Day of Week column.
4. Click Sort by column again and choose Day of Week Number.

Your date table is now properly configured for both analysis and correct sorting in visuals. You can also hide the "number" columns from the Report view by right-clicking them in the Fields pane and selecting "Hide" so that report users don't get confused.

A Quick Reference and an Alternative Method

You've now learned the fundamental way to add attributes to a date table. For future reference, there's a huge variety of columns you can add.

Additionally, there is a more compact way to create the table and all its columns in a single DAX expression using the ADDCOLUMNS function. This function takes a table as its first argument and then a series of name/expression pairs to define the new columns.

Here is how our entire Date table could have been written in one go:

Date = 
ADDCOLUMNS (
    CALENDARAUTO(),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month Name", FORMAT ( [Date], "MMMM" ),
    "Quarter", "Q" & QUARTER ( [Date] )
)

For now, the one-by-one column creation you did is perfectly fine and often easier to debug. But it's good to be aware of this more advanced technique.

Conclusion

Fantastic work! You have transformed a simple list of dates into a versatile and powerful dimension table that is ready for analysis.

Key Takeaways:

• You can enrich your date table by adding calculated columns for attributes like year, quarter, month, and day.
• Simple DAX functions like YEAR(), MONTH(), and QUARTER() extract numeric parts of a date.
• The FORMAT() function is a powerful tool for creating custom text representations, such as full month names ("MMMM") or day names ("dddd").
• To ensure chronological sorting for text columns like Month Name, you must create a corresponding numeric column (e.g., Month Number) and use the Sort by Column feature.

Preview of the Next Lesson:
Our Date table is now complete, but it's still an island in our data model. It isn't connected to our Sales data. In the next lesson, we will complete our basic star schema by establishing the relationship between the Date table and the Sales fact table. This final step will unlock the ability to perform true time intelligence calculations.