A date table is extremely important to perform Time intelligence functions in Power BI. You can use any one of the following techniques to create a date table in Power BI:
- Source table
- Using M query
- Using DAX expressions
- Auto Date/Time
Date table created at source
In many cases, data warehouses and database administrators will have already created a dedicated date table in the source system. When such a date table is available, it is recommended to use the existing table rather than creating a new one within Power BI. This ensures consistency across the data model, avoids redundancy, and can simplify the overall data architecture. Moreover, source-created date tables often come pre-configured with custom fiscal periods, holidays, and other business-specific details that are crucial for accurate time intelligence reporting.
Creating a date table using M Query in Power BI
To create a Date table using the power Query editor using M Query, go to the New Source option in the file ribbon and select Blanks Query. You can type a code in M like this:
= List.Dates (#date (2024,05,31), 365*10, #duration(1,0,0,0))
After you have written your M code, you should have your date table with a date column ready. You can then mark it as date table by going to the model view selecting the table and right clicking on the fields pane mark as a date table.
Creating date table using DAX
You can create a date table using DAX Data Analysis Expression. DAX is Used to create expressions and formulas in Power BI. We will discuss more about DAX in the upcoming blogs. To create a date table using DAX, First click on create new table option in the Table Tools in data view.
You can then enter a DAX expression in the formula bar to create a new table.
You can type in the following DAX Expression:
The following functions can be used to create different columns within a date table:
- Calendar function: It is used to create a table with a single column for dates. It requires two parameters: <start date> and <end date> parameters.
It has the following syntax:
CALENDAR(<start_date>, <end_date>) - Calendar auto function: It is used to create a table with a singular column for dates. The range is auto detected based on the range of dates within our dataset.
It has the following syntax:
CALENDARAUTO([fiscal_year_end_month]) - WeekNum: It is used to create a Week column that indicates the week number.
It has the following syntax:
Week Num = WEEKNUM (Dates [Date]) - Month: Used to create a month column in the table.
It has the following syntax:
Month Num= Month (MONTH (Dates [Date])) - Format function: This function can be used to change the format in which the date column appears within the table. In the following example, We are formatting the date column to appear as a name.
Day of the Week= FORMAT (Dates [Date], “DDDD”)
5. Using Auto/Date time:
Auto/date time in Power BI is a built-in feature that automatically creates hidden date tables for any date field in your data model. This allows users to quickly perform date-related calculations, such as year-to-date (YTD), quarter-to-date (QTD), or month-over-month (MoM), without explicitly creating a dedicated Date dimension table.
However, its recommended to only keep auto date/time option selected when you have simplistic model requirements.
