Date Hierarchy in Power BI: Ultimate Guide for Users

Date hierarchy in Power BI is one aspect that most users struggle with. If you’re like most people, you’re probably spending much time trying to create a date hierarchy.

This guide dives right into what date hierarchy in Power BI is, why it is important, and how to create one.

Table of Contents:

1. What is the Date Hierarchy in Power BI?
2. Why is the Power BI Date Hierarchy Important?
3. How to Create a Date Hierarchy in Power BI?
4. How to Use Date Hierarchy in Power BI?
5. How to Remove Date Hierarchy in Power BI?
6. How to Visualize Your Data Using Power BI?
7. What are the Benefits of Date Hierarchy in Power BI?
8. What are the Limitations of Date Hierarchy in Power BI?
9. Wrap Up

First…

What is the Date Hierarchy in Power BI?

Date hierarchy in Power BI is a feature that helps organize date-based data into a hierarchical structure. This will, in turn, make it easy for the user to analyze and navigate the data. With date hierarchy in Power BI, users can drill down from the year to the month, day, or hour level. It all depends on the user and the level of detail they want.

Date hierarchy offers an intuitive and logical method for understanding and examining trends.

Why is the Power BI Date Hierarchy Important?

Date hierarchy plays a role in the efficient data visualization and analysis of data. It allows users to drill down into time-based data, enabling effective trend analysis. This way, users can navigate and group data by year, quarter, month, or day, making it easier to identify patterns, trends, and seasonality over time.

Date hierarchy simplifies how calculations are made. Calculations are measured using time-based dimensions, and that improves the overall user experience. The model empowers users to make data-driven decisions.

How to Create a Date Hierarchy in Power BI?

There are cases where you’ll have to use the relationship editor in Power BI to create a custom date hierarchy. Here are easy steps to do just that.

• Step 1: Click the date field and choose “Create Hierarchy”

During the creation process, the original field stays intact while a new field is generated. The hierarchy is always separate even though it’s related to the content of the original field.

The new hierarchy is shown below the field it was created from. And a name is automatically assigned by Power BI. The naming is dependent on the original field name, and “Hierarchy” is attached at the end of it.

To expand the field, click the arrow on the left. You’ll notice that only one field is assigned to it.

• Step 2: Generate Custom Date Columns

You should use functions like DAY, MONTH, WEEKNUM, or YEAR to create new DAX columns in DAX format. You can also generate columns using imported SQL or Power Query. The formula and method will be dependent on the use case.

• Step 3: Assign Fields to the Hierarchy

Adding more fields to the date hierarchy involves right-clicking on your desired field, and choosing “Add to hierarchy.”

You have to start with the highest level of hierarchy, and fields have to be added in sequential order from the highest to lowest. All columns that should be added have to be present in the data model, and also be part of the date table. It could also be part of the non-date categories.

• Step 4: Test the Hierarchy in Your Dashboard

Assign the date hierarchy to the visuals in your dashboard or report. You’ll have to ascertain that they’re working. Since date hierarchies are dependent on the relationships between custom DAX formulas and date tables, you have to ascertain that the drill-down and expand features are working before you publish the report.

How to Use Date Hierarchy in Power BI?

Here’s a concise guide to help you use date hierarchy in Power BI.

• Automatic Date Hierarchy

  • Import Data: Upload the dataset that contains the date fields.
  • Enable Date Hierarchy: A date hierarchy is automatically created by Power BI for any date field. You can find it in the Fields pane beneath the date column.
  • Utilize in Visuals: Drag the date field to the visual (like a chart or table). To expand (or collapse) levels in the hierarchy, navigate to the visual header and click the expand/collapse buttons.

• Manual Date Hierarchy

  • Create Columns: If you’re looking for a custom hierarchy, you should try creating new columns for Year, Quarter, Month, and Day.
  • Create Hierarchy: Navigate to the Fields pane, and right-click in the original date field. After that, choose “New hierarchy,” and add your newly created columns to the hierarchy.
  • Apply in Visuals: For tailored data analysis, use the custom hierarchy in the visuals. This allows users to drill down into time-based data, enabling effective trend analysis. By navigating and grouping data by year, quarter, month, or day, users can easily identify patterns, trends, and seasonality over time.

• Adjusting Date Hierarchy

  • Format Options: Navigate to the Format pane of the visual to adjust the format of the date hierarchy.
  • Drill-Down Features: The drill-down options in visuals are used to interactively explore data at different hierarchy levels.

How to Remove Date Hierarchy in Power BI?

• Direct Field Usage

Remove Default Hierarchy: When a date field is dragged to the visual, the date hierarchy will be automatically applied by Power BI. You can remove it by clicking on the drop-down arrow that’s next to the date.

Select Date Only: Select the option that displays the date without hierarchy (like “Date only” or “Date”). This removes the hierarchy and showcases the data as a single date value.

• Disable Auto Date/Time for New Models

Navigate to Options: Go to File > Options and Settings> Options.

Disable Auto Date/Time: Beneath the “Current File” section, choose “Data Load” and uncheck “Auto Date/Time for new files.” This stops Power BI from automatically creating date hierarchies in any new files.

• Adjust Existing Models

Revert Hierarchy: If there are existing models, replace the hierarchical date fields with the original date column. The original date field is found without hierarchy in the Fields pane.

How to Visualize Your Data Using Power BI?

Stage 1: Logging in to Power BI

• Log in to Power BI.
• Enter your email address and click the “Submit” button.

• You are redirected to your Microsoft account.
• Enter your password and click “Sign in“.

• You can choose whether to stay signed in.

• Once done, the Power BI home screen will open.

Stage 2: Creating a Data Set and Selecting the Data Set to Use in Your Chart

• Go to the left-side menu and click the “Create” button.
• Select “Paste or manually enter data“.

• We’ll use the sample data below for this example.

Country	Revenue Stream	Revenue (in $)
USA	Digital Advertising Revenue	39,620,000
USA	Event Marketing Revenue	10,670,000
USA	Content Marketing Revenue	5,580,000
USA	Print & Outdoor Revenue	455,270
UK	Digital Advertising Revenue	40,710,000
UK	Event Marketing Revenue	24,770,000
UK	Content Marketing Revenue	6,330,000
UK	Print & Outdoor Revenue	552,190
DNK	Digital Advertising Revenue	47,040,000
DNK	Event Marketing Revenue	29,070,000
DNK	Content Marketing Revenue	7,740,000
DNK	Print & Outdoor Revenue	600,690
DNK	Media Relations Revenue	106,430
AUS	Digital Advertising Revenue	53,790,000
AUS	Event Marketing Revenue	38,530,000
AUS	Content Marketing Revenue	6,590,000
AUS	Print & Outdoor Revenue	9,040,000
AUS	Media Relations Revenue	6,130,000
FR	Digital Advertising Revenue	57,860,000
FR	Event Marketing Revenue	50,450,000
FR	Content Marketing Revenue	3,560,000
FR	Print & Outdoor Revenue	18,790,000
FR	Media Relations Revenue	15,460,000
IND	Digital Advertising Revenue	60,470,000
IND	Event Marketing Revenue	63,200,000
IND	Content Marketing Revenue	2,080,000
IND	Print & Outdoor Revenue	29,500,000
IND	Media Relations Revenue	30,020,000

• Paste the data table above into the “Power Query” window. After that, choose the “Create a dataset only” option.

• Navigate to the left-side menu, and click the Data Hub option. Power BI will populate the data set list. If no data set has been created, you’ll get an error message. Next, click “Create report“.

• To see the chart metrics, click Expand All. After that, you’ll be able to check the dimensions and metrics.

• Click Get more visuals, and search ChartExpo. Next, choose the Comparison Bar Chart.

• Click Add.

• You’ll see the Comparison Bar Chart in the visuals list.

• Click on License Settings and add the key. After adding the key, the comparison bar chart will be displayed on your screen.

• Here’s how the Comparison Bar Chart will look in Power BI.

Insights

Here are three insights from the chart.

• The country with the highest total revenue is India. They’re followed by France, Australia, and Denmark.
• “Digital Advertising” is a significant revenue contributor in most countries. That’s not the case in India where “Event Marketing” is leading.
• Countries like the US and UK do not have a “Media Relations” revenue stream.

What are the Benefits of Date Hierarchy in Power BI?

• Simplified Analysis: It allows drill down from years to quarters, months, and days.
• Enhanced Visualization: It automatically groups dates, and improves clarity and insight in visuals.
• Time-Based Aggregation: It facilitates comparison and aggregation over different periods.
• Quick Insights: It helps the user to quickly identify patterns and trends across multiple date levels.

What are the Limitations of Date Hierarchy in Power BI?

• Default Hierarchy Constraints: There is no room for custom hierarchies, and it’s limited to Year, Quarter, Month, and Day.
• Performance Impact: Automatic hierarchy generation slows down performance with large datasets.
• Formatting Issues: There is limited flexibility in date formatting within the hierarchy.
• Auto Date/Time Model: Automatically created hierarchies tend to add unnecessary complexity and clutter the model.

FAQs

How do you Create a hierarchy for a date field in Power BI?

Navigate to the Fields pane and right-click on the date field. Choose “New hierarchy.” Next, add levels like Year, Quarter, Month, and Day.

What is the date hierarchy used for?

The date hierarchy is used for organizing date-related data into predefined levels (like Year, Quarter, Month, or Day). This enables the user to easily navigate, aggregate, and analyze time-based information in visuals.

How can I access the date hierarchy in Power BI?

To access the date hierarchy, you’ll have to drag the date field into the visual. It automatically applies the hierarchy, and it becomes visible in the Fields pane under the date column.

Wrap Up

Date hierarchy in Power BI helps in organizing date data into levels like Year, Quarter, Month, or Day for easy analysis. It’s of great value for users who want to draw time-based insights. To create it, you’ll have to drag the date field into the visual.

Date hierarchy offers a consistent route to help users handle date fields across multiple dashboards and reports. It also helps the user to save time as there is no need for manual column creation. The drill-up and drill-down functionalities in visuals improve how the user interacts with the data.

Now you understand how date hierarchy in Power BI works, how will you incorporate it into your data analysis?