When a Power BI date hierarchy is not working or showing properly there are a few different troubleshooting methods you can take. These range from checking the visual fields to ensure that the hierarchy is enabled, automatically or manually rebuilding the hierarchy to double checking that time intelligence is enabled for your Power BI report file.
Table of Contents
Fix Power BI Date Hierarchies Not Showing
To fix a date hierarchies not showing in Power BI there are several things to check in the setup of your fields, visuals, relationships and reports. We typically start at the lowest level and work our way up while troubleshooting. Here are some things to look out for.
Toggle Hierarchy On / Off
When a Date field is assigned to a visual in the field well, you can toggle between Date and Date Hierarchy by left clicking on the dropdown to the right of the field name. This allows you to see individual dates, or you can see the grouped dates for year, quarter, month, day etc.
For this to work a date field must be setup as a date hierarchy to begin with. You can tell by looking at the date field in the data panel. Dates with no hierarchy will not have a calendar icon to the left of them.
Auto Re-create Hierarchy
To generate a hierarchy from a single date field, right click on the date field in the data panel and select “Create Hierarchy”. This step generates the default year, quarter, month, and day hierarchy based on the dates in the data set.
For this to work appropriately, you may need to connect your date field to a date table so Power BI can perform the task based on a continuous series of dates.
Manually Create a Hierarchy
Power BI users can manually create date hierarchies by generating separate fields for months, days, quarters or custom time periods using DAX. The fields can then be assigned to a custom hierarchy by right-clicking on a field name in the relationship editor.
You can learn more about creating date hierarchies in Power BI here.
Enable Time Intelligence
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.
Be careful when using this option as it can break other visuals in your report. Double check any visuals that were added previously before disabling or enabling this option.