Automatic Date Table with additional columns in DAX | Power BI | Chapter 6

Arpita Ghosh
May 4 · 4 min read

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

Image from Unsplash

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

  • 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
US Superstore Data from Kaggle
Image by author
  • Click on Load and save data.

Automatic Date Table

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

TODAY() DAX Function Power BI
Image by Author

Week Starting Sunday and Week Starting Monday

Image by Author

Is Working Day

Image by Author

Multiple Date columns in the Data Model

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

Multiple Relationships to the Date table

Relationships in Power BI
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
USERELATIONSHIP CALCULATE DAX Power BI

Use of multiple Date Tables

  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

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

Video

Conclusion

  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.

If you have any questions related to this project, please feel free to post your comments.

Please visit my website for other technical resources.

https://arpitatechcorner.com/

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

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data…

Sign up for Analytics Vidhya News Bytes

By Analytics Vidhya

Latest news from Analytics Vidhya on our Hackathons and some of our best articles! Take a look.

By signing up, you will create a Medium account if you don’t already have one. Review our Privacy Policy for more information about our privacy practices.

Check your inbox
Medium sent you an email at to complete your subscription.

Arpita Ghosh

Written by

Data Analyst , Blogger, https://arpitatechcorner.com/

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Arpita Ghosh

Written by

Data Analyst , Blogger, https://arpitatechcorner.com/

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store