How to Create a Date Hierarchy in Power BI?

Do you know what a Date hierarchy in Power BI is? In this tutorial, I will explain everything related to the Power BI Date Hierarchy, like what a date hierarchy is and how to create a date hierarchy in Power BI.

Also, we will discuss how to remove the date hierarchy in Power BI.

Also, I will explain to you how to use a date hierarchy in Power BI and the Benefits of a date hierarchy in Power BI with various scenarios.

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:

How to Create a Date Hierarchy in Power BI

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.

Date Hierarchy in Power BI

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.

create a date hierarchy in power bi

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

How can I make Power BI Date hierarchy

3. The year hierarchy is created.

How to Make Date Hierarchy in Power BI

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.

How to Create a Custom Date Hierarchy

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

Creating a Date Hierarchy in Power BI

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

how to create date hierarchy in power bi

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

Power BI create date 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

ColumnsData Types
Order DateDate and time
RegionSingle line of text
CategorySingle line of text
Sub-CategorySingle line of text
Product NameSingle line of text
SalesCurrency
QuantityNumber
use date hierarchy in power bi

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.

automatic date hierarchy power bi

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.

How to use Date Hierarchy in Power BI

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.

auto create date hierarchy in Power BI

To remove auto date hierarchy, follow the below steps:

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

remove date hierarchy power bi

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

power bi remove date hierarchy

3. Then you see the date hierarchy removed.

Power BI display date without hierarchy

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

Benefits of a Date Hierarchy in Power BI

Now, I tell you the benefits of using date hierarchy in Power BI.

  • The Power BI users can easily navigate dates by year, quarter, month, and day in the visualization.
  • The Power BI data hierarchy allows the visualization to drill down into time-based data.
  • The Power BI data hierarchy features make the data display more convenient and intuitive.

Conclusion

I hope this tutorial helps you understand the date hierarchy in Power BI. In this Power BI tutorial, I explained a date hierarchy, how to create a date hierarchy in Power BI, and how to remove a date hierarchy in Power BI.

Also, I explained how to use date hierarchy in Power BI and the benefits of using date hierarchy in Power BI.

Moreover, we may also like some more Power BI tutorials: