Creating a Date table in Power BI

In this article, we will generate a date table in Power BI which will be a reference point for data models that can be used for time intelligence calculations.

Creating a date table is very easy with DAX functions. A date table is essential in creating data models. With a date table, we can create Tables, Charts & Slicers in reports.

Now let’s start building a Date table called Date master for 2021 -2022. This date table will include Date, Year, Month, Week, Day, Quarter, Weekday, Weeknumber, Day Name & Month Name by using DAX functions.

Step-A: Click on Data on the Home page

Step-B: From the Table tools ribbon, click New table

Step-C: Once we click on the new table, we will get the option to write the function. Now let’s write a DAX measure like the below screenshot to generate the calendar of 2022 in the table

Ex: Tablename = Calendar(“Startdate”, “Enddate”)

Step-D: The table will generate date in a single column, and Datemaster will appear in your data Fields.

Step-E: Click on the new column and write the Dax formula to generate Year, Month, WeekDay, Day-Name using DAX functions.

Step-F: Let us create new columns with DAX measures by giving using formulas as shown below:

1) Year: Year = YEAR([Datemaster[Date])
DAX Formula: Year = YEAR(Tablename[Datecolumn])

2) Month : Month = MONTH([Datemaster[Date])
DAX Formula: Month = MONTH(Tablename[Datecolumn])

3) Day: Day = DAY([Datemaster[Date])
DAX Formula: Day = DAY(Tablename[Datecolumn])

4) WeekDay – To know which day (1-7) it belongs in a week.

Weekday = WEEKDAY([Datemaster[Date])

DAX Formula: weekday = WEEKDAY(Tablename[Datecolumn])

5) Weeknum – To know the number of the week (1-52) it belongs in the year.
DAX Formula: Weeknum = WEEKNUM(Tablename[Datecolumn])

6) Quarter – To know which quarter the week or month belongs in the year.
DAX Formula: Quarter = Quarter(Tablename[Datecolumn])

7) Dayname – To know the day name of the week(Sunday – Saturday), We need to use the Format function to extract the date in word format. To get a full-day name, we need to use “DDDD” (Example: Sunday, Monday). If you need a short day name, then you can type “DDD” (Example: Sun, Mon)
DAX Formula: Dayname = FORMAT(Tablename[Datecolumn])

8) MonthName – To know the month in word format (January – December), We need to use the Format function to extract the date in word format. To get the month name in full, we need to use “MMMM” (Example: January). If you need a short month name, use “MMM” which will give January as Jan/ April as Apr, etc.

DAX Formula: MonthName = FORMAT(Datemaster[Date], "MMMM")

Step-G: After creating a new table with all the above DAX formula, the table looks like this

Step-H: After creating the table, it’s essential that we need to map these tables with an existing table by linking the Date column. For example, the CR_Application table is used as a reference to link the Datemaster table, as shown below.

Step-J: In the data Fields pane, we can see the newly created table (Datemaster) along with the existing table.

Conclusion:

Creating a date table is easy, but it’s essential to understand that the DAX formula plays a vital role in showing the date in many different varieties. With the above DAX functions, we can create a date table that helps generate a Dashboard or reports. This is the standard procedure to create a date table, which can save you time in the query editor.