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.
- What happened?
- Where are we now?
- 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.
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
- 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.
- Go to Data → under Table-tools Click on New table
2. To create the Date table with other required columns, write the below code and enter.
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.
Week Starting Sunday and Week Starting Monday
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.
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
- Create relationships between Date table US Superstore Data table.
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.
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.
- Follow the same steps to create date tables for order date and ship date.
- For better representation, provide a proper naming convention.
3. Create one report to verify your changes.
Please find the code in the below location
DAX — Chapter 6 https://github.com/arpitag1/Power-BI
In this blog, the following things have been captured
- How to create an automatic Date Table
- Adding more columns to Date Table
- 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.