How to Make Automated Date Table with additional columns in DAX — DAX in Power BI — Chapter 6

Dealing with Automatic Date Table, additional columns in date table and handling multiple date columns in Data Model.


For any data analysis project, three questions are mandatory to ask.

  1. What happened?
  2. Where are we now?
  3. What are we going to do?

The answers to these questions depend on date and time data but in different forms (historical, current stage and forecast). 

DAX helps to manage any date and time information in an organised way with useful reports.

Get Data

I consider the US Superstore dataset from Kaggle.

  • Let’s start with the Get Data option under the Home tab. As this is a CSV file, select the Text/CSV option from the drop-down list
  • Select the file named US Superstore data.csv
  • After selecting the file, data will be displayed in the below format
US Superstore Data from Kaggle
Image by author
  • Click on Load and save data.

Automatic Date Table

As you observe, for any analytics project, you need a Date table in the data model. To create a Date table automatically there are two functions, CALENDER() and CALENDERAUTO(). Now, what is the difference between them? 

For the CALENDER() function, you need to provide the start date and end date. Whereas, for CALENDERAUTO(), Power BI automatically finds the start year and end year from the data model where some date fields exist.

Let’s see one example with CALENDERAUTO() and find how other relation date and time functions can be executed.

  1. Go to Data → under Table-tools Click on New table
Date Table DAX Power BI
Image by Author

2. To create the Date table with other required columns, write the below code and enter.

CALENDERAUTO() DAX Power BI Date Table
Image by Author

3. You can create columns step by step. That means first date table creation, then creating others columns like the year, quarter, month etc. But using ADDCOLUMNS() DAX function you can create a complete table in one go. 

4. Here, you used other functions like YEAR, MONTH, WEEKDAY including FORMAT.

Additional Columns to Date Table

Days from Today

If you have to show some report with the most recent 15 days or 30 days information, you can use this type of DAX expression.

TODAY() DAX Function Power BI
Image by Author

Week Starting Sunday and Week Starting Monday

Image by Author

Is Working Day

If you want to generate a report based on working day information, then this DAX expression will help you to achieve it.

Image by Author

Multiple Date columns in the Data Model

In your data model if you have multiple date fields, then you need to think about two options, creating multiple relationships with the same date table or creating multiple date tables.

In the current example, you have sales information with two date fields, order date and ship date.

Multiple Relationships to the Date table

  1. Create relationships between Date table US Superstore Data table.
Relationships in Power BI
Image by Author

2. Now, if you want to create two measures based on different relationship to the date table, then can use USERELATIONSHIP function with CALCULATE function.

USERELATIONSHIP CALCULATE DAX Power BI

Use of multiple Date Tables

Instead of creating duplicate fields for each measure, you can create different date tables, for example, one for order date, another for ship date. From a maintenance point of view, it is a better solution, but it is complex to display ordered sales and shipped sales in the same report.

  1. Follow the same steps to create date tables for order date and ship date.
  2. For better representation, provide a proper naming convention.
Date Tables DAX Power BI
Image by Author

3. Create one report to verify your changes.

Date Table DAX Power BI
Image by Author

Download

Please find the code in the below location

DAX — Chapter 6 https://github.com/arpitag1/Power-BI

Video

Conclusion

In this blog, the following things have been captured

  1. How to create an automatic Date Table 
  2. Adding more columns to Date Table
  3. Handling multiple date columns in a Data Model

In my next blog, we will learn more about DAX.

Please like, comment and subscribe to my YouTube channel which you have already seen. 🙂 Keep Learning.

7 comments

  1. As a biginer, this type post makes me to learn the complex concept in a simple way… kind of joy in learning… Thanks you author..

    Liked by 1 person

  2. As a beginner, this type post makes me to learn the complex concept in a simple way… kind of joy in learning… Thanks you author..

    Liked by 1 person

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: