Remove a date hierarchy in Power BI to view data at the day level and avoid aggregations. Hierarchies can be controlled at the report or individual visualization level. Users can also create custom hierarchies to view data in a way that fits your business needs.
We’ll also explore how to troubleshoot and fix times when date hierarchies are not showing, and what to do if your business doesn’t operate on a standard 12/31 year end.
To eliminate a date hierarchy from a visual, click the arrow beside the date field on the visual and select “Date” instead of “Date Hierarchy.” This displays the finest granularity, usually the day level. To completely delete the hierarchy, right-click the date hierarchy and choose “Delete From Model” in the fields pane.
Date hierarchies in Power BI are an integral part of building robust visuals that people can drill into.
Table of Contents
Let’s jump in and look at how to work with Date Hierarchies!
What is a Date Hierarchy and Why You Should Use Them
While date hierarchies are by far the most common, you can add any set of fields into a custom hierarchy as long as a relationship exists between them.
The grouping of broad categories to more precise categories is akin to viewing categories or time through a magnifying glass. Each closer look, you uncover more detailed intervals.
They offer report readers a number of benefits.
- Simplified Navigation – Easily navigate through time or category by drilling down or rolling up to view data at different levels of granularity.
- Richer Insights – Extract more profound insights from your data by comparing year-to-year, quarter-to-quarter, or even day-to-day metrics.
- Visual Appeal – Hierarchies keep visuals organized and more aesthetically pleasing.
- Flexibility – Gives users the ability to see data at an annual, monthly, or daily basis depending on their needs.
Date hierarchies make it easier for users to tell stories and understand their data. Insights may only be visible as a long term trend, or in granular detail.
Hierarchies make it possible to provide one report that does both.
How to Remove Date Hierarchies in Power BI
To disable a date hierarchy in Power BI click on the down arrow on the name of the Date Column or Row in your visual. A contextual menu will appear where one can toggle between a Date and a Date Hierarchy.
This change will take effect for the single visual that you are working on, and maintain the hierarchy for all other visuals within your report.
Note: If you wish to remove only part of a date hierarchy, you can choose to remove years, quarters or days from the hierarchy. This functionality is incredibly useful if you do not intend on doing quarterly analysis or wish to prevent users from seeing daily data. For example, when all data is dated as of the end of a month.
Click the X to the right of the hierarchy to remove a section of it. These can also be unselected from the data panel view.
You can delete a date hierarchy completely, by right clicking on it and choosing Delete from Model. This only deletes the hierarchy, the underlying date data remains in the model.
How to Fix Power BI Date Hierarchies Not Showing
If you defined a column as a date data type in the Power Query editor, but Power BI does not automatically generate a date hierarchy, check the Power BI Options. It could either be disabled Globally for all files, or for the Current File
Navigate to File > Options > Data Load for both Global and Current File, ensure that Auto Date/Time is enabled under the Time Intelligence section.
The Auto Date/Time Option automatically generates a hidden date table in Power BI for each field that is detected as a date and automatically creates a relationship between the field and hidden table. The date table is used to create a default date hierarchy for each field.
Note: There are times that you may want to disable Auto Date/Time
- Auto Date/Time may hinder performance when working with large datasets.
- You could be utilizing a custom date table, for instance when your business runs on a year end other than 12/31
How to Manually Create a Date Hierarchy in Power BI
To start, right click on a date field and choose Create 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.
This generates a new row called Date Hierarchy.
If you expand it by clicking the arrow to the left, you’ll see that there is only one field in it.
To add more fields to a Date Hierarchy, right click on the field you want to add. Choose Add to Date Hierarchy.
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.
There are a couple of considerations to keep in mind when creating custom hierarchies.
Rearranging Date Hierarchies – You must add fields in the order that they will be drilled down into. Start with the highest date range, such as year, then add subsequent units like halves, quarters etc. Once you start creating a date hierarchy, you cannot rearrange fields or delete single ones. The date hierarchy will need to be removed from the model and re-created from the beginning.
Changing Date Hierarchy Names – To rename levels of a date hierarchy, double click into the field name. You will be given a text box that lets you rename each level of the hierarchy.
If you’d like an even more visual appraoch to learning about date table, the Guys in a Cube cover the topic very well.
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.
How to Change Date Formatting in Power BI
T change the format that a date is displayed in Power BI, select the field name that you want to update. In the Power BI Ribbon, you can use the Column Tools section to update bot the data type between data and date/time as well as the actual format that the date is displayed as.
Note: Column tool options may not be available when using a live connection or direct query with a Power BI data source.
Conclusion
Date hierarchies are an important part of creating reports in Power BI. For most users, they can be automatically generated and it will work out okay. For businesses that have custom quarters, and fiscal years it might be easier to disable them globally under options and created with custom date tables.