How to Create a Date Table in Power BI?

Do you want to create a date table in your Power BI Desktop? In this tutorial, I will tell you what a date table in Power BI is and how to create a date table in Power BI [without using DAX].

Also, I will tell you how to create a date table using Power BI DAX.

What is a Power BI Date Table?

A Power BI Date table is like a unique calendar that helps organize and analyze our data based on dates. It contains information like days, months, and years and helps create time-based reports in Power BI.

Refer to the screenshot below for what a Power BI Date table looks like:

How to Create a Date Table in Power BI

This is an overview of the Power BI Date table.

How to Create a Date Table in Power BI

There are three ways in which we can create a date table in Power BI:

  • Create a Power BI Date Table using Auto Date/Time (Source Data).
  • Create a Power BI Date Table using DAX.
  • Create a Power BI Date Table using Power Query.

Power BI Create Date Table Auto Date and Time

Here, I have a SharePoint list (Amazon Go). This list has an Order Date column, which is a Date and Time data type, and some other columns, as you can see in the screenshot below:

Create date tables in Power BI Desktop

When I connect the SharePoint List in Power BI Desktop, the Order Date automatically hierarchy in the Data panel is called the Date Hierarchy, which includes Year, Quarter, Month, and Day. Check out the screenshot below.

How to Create Date Tables in Power BI Desktop

In this case, you don’t need to create a date table in Power BI.

If this date hierarchy does not show, you must first enable “Auto date/time” in Power BI. Follow the below steps:

1. Open Power BI, then click File -> Options and Settings -> Options.

Auto Datetime hierarchy not working

2. Click on Current File -> Data Load -> Time Intelligence -> Enable Auto date/time -> OK, then refresh the data set.

Solved Date hierarchy not available in Power BI

This way, you can create a date table with an auto-date and time in Power BI.

Create a Date Table in Power BI using DAX

To create a date table using DAX, we need to use the DAX function “CALENDAR.” The Power BI CALENDAR function helps us create the date table by specifying the start and end dates.

Syntax:

TableName = CALENDAR (Date(Start Date),Date(End Date))

Example:

Create a Power BI date table from 01 Nov 2023 to 30 Nov 2023. Follow these simple steps to achieve this:

1. Open Power BI Desktop. Then click “New table” under the Modeling tab.

How to Create a Date Table from Scratch in Power BI

2. Now, use the below DAX formula to create a date table from 01 Nov 2023 to 30 Nov 2023. Then, click Commit on the left side of the formula bar.

Date Table = CALENDAR(Date(2023,11,01),Date(2023,11,30))
How to Create Date Table in Power BI

3. Go to the Table view. You can see a Date column added to the table. Check out the screenshot below.

Example to create date table in Power BI

Now, I will tell you how to add more columns like Year, Month, Quarter, and Day to the above Date table. First, we add a year column.

1. Click the New Column under the Table tools in the same window.

How to add new column in Power BI

2. Add the below DAX formulas in the formula bar. Click Commit on the left side of the formula bar.

Year = YEAR('Date Table'[Date])
power bi create date table with year

3. Now, you can see a Year column added to the date table, as shown below.

create date table using power bi dax

4. Follow the above three steps to add new columns like Month, Quater, and Day, using the below DAX formula.

Month = FORMAT('Date Table'[Date],"mmmm")
Quater = QUARTER('Date Table'[Date])
Day = DAY('Date Table'[Date])

Our date table is shown in the screenshot below, using the DAX formula.

How to create a date table in Power BI in simple steps

You can create a Power BI date table using DAX using the above steps.

Create Date Table in Power Query Power BI

Now, I will show you how to create a date table using a power query in simple steps:

This date table shows the start date as 31 Aug 2016 and the end date as 19 Jun 2018.

1. Open Power BI Desktop. Click Transform data under the Home tab.

Date Table for Power BI using Power Query

2. In the Power Query Editor, click the Home tab -> New Source -> Blank Query.

Create Date Table or Calendar in Power Query M

3. On the right side, give the name of the query. In my case, it’s the PowerQuery Date Table.

how to create a blank query in Power BI

I am using the”List.Dates” function to create a “Date Table” in the power query.

Syntax :

List.Dates(start as date, count as number, step as duration)

Example :

4. Enter the below code to create a date table from “2016-08-31” to “2018-01-19.” Then click the commit button.

= List.Dates(                                                          //Creates a list of dates
      #date(2016,08,31),                                          //Starting from Aug 31th 2016 
      Number.From(#date(2018,01,19)-#date(2016,08,31)),  //Gap between Starting date and Ending date
      #duration(1,0,0,0))                                         //Of 1 Day
Create your own Date Table using Power Query in Power BI

5. Now, the date list is created. Check out the screenshot below.

Custom Power BI Date Tables Using Power Query

6. Now, we convert the date list to the table. Click on To Table in the Convert group.

How to convert list into table in Power Query Editor

7. Here, change the name of Column 1 to Date and set the data type to Date using the screenshot below.

3 Ways To Create Date Table In Power BI DAX

8. We add columns like year, month, quarter, week, and day. Click on Add Column -> Date.

Then, a small dialog box opens; using this dialog box, we add columns. Check out the screenshot below.

Using Power Query to create a Date table

9. We add the “Month” column to the Date Table. Select the Date column, then click Add Column -> Date -> Month -> Name of Month.

how to create a date table in power bi

10. Here, I want the month column to display a particular name of the month. You can choose only “Month.”

Creating a Date Dimension with Power Query

11. Similarly, we can add year, quarter, and day. Check the screenshot below.

Create a Date Time calendar in power BI

I hope you follow the above steps to create a date table in the power query editor.

Conclusion

In this tutorial, we learned what is a date table in Power BI, how to create a date table in Power BI, what are the different ways to create a date table in Power BI, and how to create a date table in Power Query and DAX.

Also, you may like some more Power BI tutorials: