How to Create a Date Table in Power BI?

When working with dates and making project plans in Power BI, you may encounter scenarios where creating a date table becomes necessary.

There are multiple methods to create or add a date table in Power BI.

In this article, I will explain what is a Power BI Date table and how to create a date table in Power BI desktop. Additionally, I’ll guide you through creating a date table with three different methods. Such as:

• Create a Power BI Date table using the auto-date time
• Create a Power BI Date table using DAX
• Create a Power BI Date table using Power Query
• Create a Date Hierarchy in Power BI

What is a Power BI Date Table?

In Power BI, a Date Table is a special type of table that contains dates and related information, such as the day of the week, month, quarter, and year.

This table helps in creating visualizations like time series charts, comparisons over time, and other date-related analyses easily and efficiently.

Example:

Imagine you run a small retail store that sells clothing. You want to analyze your sales data to understand your business performance over time and identify trends.

This includes tracking your daily, weekly, and monthly sales, comparing sales performance between different product categories, and analyzing seasonal trends.

Suppose you have a date table and sales data. In that case, you can use Power BI to create visualizations like line charts to track sales over time, bar charts to compare sales between product categories, and slicers to filter data by specific periods.

Now you understand how important a date table is in Power BI.

Check the screenshot below for an example of a date table.

How to Create a Date Table in Power BI?

As I suggested above, we can create a date table in Power BI using three different ways. Let’s discuss it one by one.

1. Power BI Create Date Table Auto Date and Time

One simple and easy way to create a date table in Power BI is by enabling the time intelligence option. Ensure that a date column already exists in your model.

Now, we see how to enable the time intelligence option in Power BI.

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

2. Click on Current File -> Data Load -> Time Intelligence -> Enable Auto date/time -> click OK button.

Now load the data and make sure that a date column exists in your data.

Here, we have a SharePoint list (2024 Sales Data) that contains below columns with various data types:

Load the data by using the “Get Data” option. After that, you will observe the date table in the “Data” pane.

This way, you can create a date table in Power BI.

2. Create a Date Table in Power BI using DAX

To create a Date Table in Power BI using DAX, we can use the CALENDAR and CALENDARAUTO functions. Both methods return a table with a single ‘Date‘ column and a list of date values.

I use the CALENDAR function to create a date table in this example.

CALENDAR function Syntax:

CALENDAR(<start_date>, <end_date>)

Where:

• start_date = Any DAX expression that returns a datetime value.
• end_date = Any DAX expression that returns a datetime value.

In this example, I Create a Power BI date table from 01 Feb 2024 to 29 Feb 2023. Follow below steps below to achieve this:

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

2. In the formula bar, Put the below DAX expression.

Date Table = CALENDAR(Date(2024,02,01),Date(2024,02,29))

Where:

• Date Table = This is the name we give to the table we create.
• CALENDAR() = DAX function generates a table of dates within a specified range.
• Date(2024,02,01) = This function creates a specific date representing February 1, 2024.
• Date(2024,02,29) = This function creates another date representing February 29, 2024.

3. Navigate to the Table view, where a Date column is added to the Date Table dataset. Check out the screenshot below.

To add more columns like Year, Month, Quarter, and Day to the Date table, follow the below steps.

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

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

Where:

• Year = This is the name given to the calculated column.
• YEAR = This DAX function extracts the year component from a date.
• (‘Date Table'[Date]) = This specifies the column containing the dates from which we want to extract the year component.

3. Now, you can observe a Year column added to the date table, as the screenshot shows below.

Follow the same process described above to add new columns like Month, Quarter, and Day using the following DAX formulas.

Month = FORMAT('Date Table'[Date],"mmmm")

Quater = QUARTER('Date Table'[Date])

Day = DAY('Date Table'[Date])

After that, the date table looks like the screenshot below.

This way, you can create a date table in Power BI using DAX

3. Create Date Table in Power Query Power BI

We can use M-Query language to create a date table in Power Query. Follow the below steps to create this:

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

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

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

We use the “List.Dates” function to create a “Date Table” in Power Query.

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

4. Enter the below code to create a date table from “2024-02-23” to “2024-03-23.” Then click the commit button.

= List.Dates(
      #date(2024,02,23),
      Number.From(#date(2024,03,23)-#date(2024,02,23)),
      #duration(1,0,0,0))

Where:

• List.Dates = This M function generates a list of dates within a specified range.
• #date(2024,02,23) = This specifies the starting date for the list of dates.
• Number.From(#date(2024,03,23)-#date(2024,02,23)) = This calculates the number of days between two dates and converts it to a number.
• #duration(1,0,0,0) = This specifies the duration increment for each step in the list, which is one day in this case.

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

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

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

Now we see how to add columns like year, month, quarter, week, and day in the Power Query editor.

8. Click on Add Column -> Date. Then, a small dialog box opens; using this dialog box, we add columns. Check out the screenshot below.

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

10. After that, you see the month column added to the Power Query Editor.

11. We can add year, quarter, and day in the same way (before adding the column, you need to select the date column). Check the screenshot below.

After that, click “Close & Apply,” You can use the date table in any visual.

Power BI Date Hierarchy

• Microsoft offers a built-in function in Power BI known as Date Hierarchy.
• In Power BI, the date hierarchy refers to how dates are organized and structured in a report or visualization.
• It allows users to easily navigate and analyze data based on different time intervals, such as years, quarters, months, and days.

Power BI Date hierarchy looks like the screenshot below:

This is the overview of the Date Hierarchy in Power BI.

How to Create a Date Hierarchy in Power BI

Now, I will tell you how to create a date hierarchy in Power BI in simple steps:

I have a Date table in this tutorial that we can utilize for this Power BI tutorial. The Date Table includes columns for Date, Month Name, Year, Quarter, and Day Name.

Note:

If you don’t know to create a Power BI date table, no problem. I have written a full tutorial about How to Create a Date Table in Power BI

1. Click the Model view on the left side of the Power BI Desktop.

2. Right-click on the top highest hierarchy (Year) -> Click on the Create hierarchy.

3. The year hierarchy is created.

4. Next, we will add the Quarter, Month Name, and Day Name to the Year Hierarchy. To do this, go to the Properties panel -> expand General -> In the Hierarchy field, add the Quarter, Month Name, and Day Name.

5. After adding the column, then click Apply Level Changes.

6. Go to the Data panel to see a Year Hierarchy created.

7. Double click in the Year Hierarchy and give the name as Data Hierarchy.

This way, we can create a date hierarchy in Power BI Desktop.

How to use Date Hierarchy in Power BI

Now we see how to use date hierarchy in the Power BI Report.

Here, I have a SharePoint list that I am using for this example

1. Open Power BI Desktop and load the data, then the data set shows in the Data Panel.

In the data set, the Date Hierarchy is automatically created.

2. Click the Stacked column chart in the Visualization panel, then drag Order Date into the X-axis and Sales into the Y-axis field.

Follow the above step to use date hierarchy in Power BI.

How to Remove Date Hierarchy in Power BI

Now we see how to remove the date hierarchy in Power BI.

There’s a Date column in the date table above, and Power BI automatically creates a date hierarchy within that column.

On the left side is a screenshot displaying the date hierarchy. On the right side is the screenshot after removing the date hierarchy how it looks.

To remove auto date hierarchy, follow the below steps:

1. Click File -> Options and Settings -> Options.

2. Click on Current File -> Data Load -> Time Intelligence -> Uncheck the Auto date/time checkbox -> Click OK.

3. Then you see the date hierarchy removed.

This way, you can remove the date hierarchy in Power BI.

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

Conclusion

I hope you can easily create a date table in Power BI.

In this short tutorial, we learned about date tables in Power BI and various methods to create them, like creating a Power BI date table using auto date time, DAX, and Power query with various scenarios.

Also, you may like some more Power BI tutorials:

• Create a Table in Power BI
• Create Measure Table in Power BI
• How to Append Two Tables in Power BI

Preeti Sahu

Preeti Sahu is an expert in Power Apps and has over six years of experience working with SharePoint Online and the Power Platform. She is the co-author of Microsoft Power Platform: A Deep Dive book. As a Power Platform developer, she has worked on developing various tools using Power Apps and Power Automate. She also makes Microsoft 365 videos and shares them on YouTube.