How to Create Date Hierarchies in Power BI

Power BI date Hierarchies can be automatically generated or created manually. Creating them automatically is by far the fastest way to allow users to navigate through visuals grouped by year, quarter, month and day but are not customizable. Manual hierarchies can contain different types of formatting, grouping, or different fiscal year ends.

Power BI Date Hierarchies Explained

Power BI Date Hierarchies create logical groups out of a series of individual dates. Power BI will automatically organize date fields into year, quarter, month and days. These groups become nested as a hierarchy which allows users to drill down from the highest level to the lowest.

The example below shows a date hierarchy assigned to a visual where values are sorted and subtotaled into year, quarter, and month.

An example of a default Power BI date hierarchy grouping dates into years, quarters, months, and days

Power BI additionally supports custom date creation for organizations that have non-calendar year ends, require weekly grouping or other custom groupings.

How to Create a Date Hierarchy in Power BI

When a date hierarchy doesn’t automatically generate, or you need to create a custom date hierarchy, you can do so using the relationship editor in Power BI. Here are the steps to creating custom hierarchies in Power BI.

Step 1.) Click on a date field and choose Create Hierarchy

When you create a hierarchy a new field will be generated. The original field stays intact, the hierarchy will be completely separate though it is related to the content of the original field.

Right click a field value and choose Create Hierarchy to create custom hierarchies in Power BI

The new hierarchy will typically be shown directly below the field it was created from. Power BI automatically assigns a name based on the original field name and appends “Hierarchy” to the end of it.

This field can be expanded by clicking the arrow on the left, but at this point only one field is assigned to it.

A new date hierarchy field that does not have any other fields assigned to it yet

Step 2.) Generate Custom Date Columns

You will need something to add to the new date hierarchy that’s being created. Create new DAX Columns using functions such as DAY, MONTH, WEEKNUM, YEAR or whichever formulas needed. Columns can also be generated using Power Query or imported from SQL. The method and exact formulas will vary based on your use case.

Step 3.) Assign Fields to the Hierarchy

To add more fields to a Date Hierarchy, right click on the field you want to add from the data panel and choose add to hierarchy. The contextual menu will show all hierarchies available on the selected table.

Richt click additional fields to add field values and drill down levels to a hierarchy

Start with the highest level of the hierarchy, and add fields that go in each sequential order from highest to lowest. All of the columns that you want to add to a hierarchy must be present in your data model and are typically part of a date table, but could also be made of non-date categories.

You can add as many columns as you want to your new hierarchy. There does need to be a relationship so that the field values are nested from highest to lowest so Power BI knows how the levels of the hierarchy are related.

Step 4.) Test the Hierarchy in Your Dashboard

The final step is to assign the date hierarchy to your visuals in a dashboard. You will want to thoroughly test that they work as expected. Because date hierarchies are typically dependent on relationships between date tables and custom DAX formulas you will want to ensure that the drill down and expand features work as expected prior to publishing your report.

Rearrange and Modify Date Hierarchies

You can create, re-arrange and modify date hierarchies using the Relationship Editor in Power BI. Go to the data model view and select the date hierarchy from your table. On the bottom of the Properties panel you have the ability to re-arrange the order of fields assigned to a hierarchy as well as add or delete them.

Example of a date hierarchy in the model view where fields can be rearranged, added or deleted.

An alternative to this method is dragging and dropping individual fields of your hierarchy into the field well of a Power bI visual.

Working with Date Tables in Power BI

If you are creating a custom date hierarchy, you will likely be working off of a custom date table. Date tables can either be generated with Power Query, DAX, imported from a SQL data source or created in Excel and brought into your data model.

Each level of a date hierarchy must be in its own separate column of a date table.

Read our comprehensive guide to building custom date tables in Power BI to learn more.

Scroll to Top