Skip to content
5MinuteBI Creating Data Power Users 5 Minutes at a time

5MinuteBI

Creating Data Power Users, 5 Minutes at a Time

  • Home
  • Blog
  • About
  • Contact
Home / Data Visualization / Power BI Data Visualizations /

How to Use Date vs Date Hierarchy in Power BI: A Comprehensive Guide (2023)

BySteve Young Updated onMay 14, 2023

Power BI is a flexible data visualization tool that helps organizations make data-driven decisions. Most Power BI date visualization problems people hit come down to how the tool handles dates. In this blog post, we will explore two crucial aspects of hierarchies in Power BI: Date and Date Hierarchy.

The feature that causes the most confusion is that Power BI has built-in functionality that will display a drillable hierarchy of year, quarter, month, and days when a column, formatted as a date, is dropped onto a visualization. This article and example will help explain.

In this Article
  1. What are Data Hierarchies in Power BI
    1. What is a Date in Power BI?
    2. What is a Date Hierarchy in Power BI?
    3. Key Differences Between Date and Date Hierarchy
  2. How to Use Date in Power BI
    1. Adding a Date Field to a Power BI Report
    2. Customizing Date Visuals in Power BI
  3. How to Use Date Hierarchy in Power BI
    1. Creating a Date Hierarchy in Power BI
    2. Using Date Hierarchy in Power BI Visuals
    3. Customizing Date Hierarchy Visuals in Power BI
  4. Conclusion
  5. FAQ
    1. Q. What is Date Hierarchy in Power BI?
    2. Q. How can I access the Date Hierarchy in Power BI?
    3. Q. Which visuals work best with Date Hierarchy in Power BI?
    4. Q. How can I customize Date Hierarchy visuals in Power BI?
    5. Q. Is it possible to drill down and up in Date Hierarchy visuals?
  6. Tutorial
    1. Drag & Drop A Data Value – – A Date Hierarchy Shows Up

What are Data Hierarchies in Power BI

Before we explore the best practices for using Date and Date Hierarchy, let’s define these terms and understand Data Hierarchies.

Before we explore the best practices for using Date and Date Hierarchy, let’s define these terms and understand Data Hierarchies.

Hierarchies in Power BI help to organize data into multiple levels, which enables users to explore data at different granularities by drilling down or up through these levels. A hierarchical structure makes it easier for users to navigate and analyze data logically and intuitively. The example below illustrates how various values can be grouped together in a hierarchy.

Hierarchies help organize data with a natural multilevel structure, such as time series, geographic regions, or organizational structures. As a result, they simplify complex data and provide a comprehensive view, enabling users to make informed decisions based on trends and patterns across different levels of detail.

The sales dataset includes transaction information for various cities and regions. The dataset is organized into a two-level hierarchy based on the Region and Facility columns in the facility table. This hierarchical structure allows users to analyze sales data at both the region and facility levels, providing a more granulated view of regional trends.

Power BI Hierarchy example with table.
Power BI Hierarchy example. The portion is blurred for clarity.

The key benefit of utilizing hierarchies in Power BI is that end-users can easily spot patterns, identify outliers, and gain valuable insights across multiple data dimensions.

Dates are handled a little differently; let’s examine this.

What is a Date in Power BI?

A Date is a data type in Power BI that represents a specific day, month, and year. It is commonly used to track time-based events, such as sales transactions or project milestones.

Examples:

  • 2023-04-15
  • 2023-01-01

What is a Date Hierarchy in Power BI?

A Date Hierarchy is a structured way of organizing dates in Power BI, allowing you to drill down from a high-level time period (such as a year) to more granular levels (such as quarters, months, or days). This provides a more in-depth view of your data and allows for more advanced analyses.

A Date Hierarchy is a powerful tool in Power BI that allows users to analyze data across different levels of granularity, such as a year to more granular levels, such as quarters, months, or days. For example, you can analyze data at the day, month, quarter, and year levels. This flexibility enables more insights into trends in your data over time.

Example:

  • Year: 2023
    • Quarter: Q1
      • Month: January
        • Day: 2023-01-01

Key Differences Between Date and Date Hierarchy

Even though both are the same original data, having a hierarchy provides a number of useful features for your analytics, such as;

  • Data structure: Date is a flat data type, while Date Hierarchy is hierarchical.  Creating the structure is an easy task in Power BI.
  • Flexibility: Date provides more flexibility in formatting and displaying data, while Date Hierarchy has predefined levels with which the user is already familiar.
  • Granularity: Date Hierarchy allows you to drill down and up through different levels of time periods while Date is limited to a single day.

How to Use Date in Power BI

Now that we understand the difference between Date and Date Hierarchy, let’s learn how to use Date in Power BI.

Adding a Date Field to a Power BI Report

  1. Importing data with Date columns: Ensure your data source contains columns formatted as a Date data type. Power BI will automatically recognize these columns and treat them as Date fields.
  2. Adding a Date slicer or filter: You can add a slicer or filter to your report to allow users to filter the data based on a specific date or range of dates.

Customizing Date Visuals in Power BI

  1. Choosing the appropriate visual type: Date fields can be used in various visuals, such as line charts, bar charts, or tables. Choose the visual type that best represents your data and the insights you want to highlight.
  2. Formatting Date display options: You can customize how the Date is displayed in your visuals by changing the format, such as using “MMM YYYY” to display “Apr 2023.”
  3. Applying conditional formatting to highlight insights: Use conditional formatting to highlight specific dates or ranges based on rules, such as showing dates with higher sales in a different color.

How to Use Date Hierarchy in Power BI

Let’s now explore how to use Date Hierarchy in Power BI.

Creating a Date Hierarchy in Power BI

There are two ways to accomplish this.

  1. Create a Date Hierarchy using a Date table: You can create a custom Date Hierarchy by adding calculated columns for different time periods (e.g., Year, Quarter, Month) to your Date table and then using the “New Hierarchy” feature in Power BI.
  2. Automatically generated Power BI Date Hierarchies for imported data: Power BI will automatically generate a Date Hierarchy for imported data containing date columns. You can access this hierarchy by expanding the date column in the Fields pane. As shown on the left below, you drag the Date Value column over to the table; you see the Column and Power BI’s generated Hierarchy date.
Drag the Table Column Value over to the Grid.
The full Hierarchy is displayed.
You can select what you want to see in the visual using the icon.
Select the Value or the Hierarchy.

This works on many compatible visuals, such as
bar charts, column charts, or treemaps.

Using Date Hierarchy in Power BI Visuals

You do not see this in all visuals. You can access a data hierarchy in 3 ways.

  • Date Hierarchies work well with visuals that support hierarchical data, such as bar charts, column charts, or treemaps.
  • You can expand or collapse different levels of the Date Hierarchy in your visual to show more or less detail.
  • Use the drill-down and up functionality to navigate through different levels of the hierarchy, allowing for in-depth data exploration.

Customizing Date Hierarchy Visuals in Power BI

Being able to navigate the hierarchies can be difficult.

  • Formatting display options for different hierarchy levels: Customize the display format for each level of the Date Hierarchy to ensure consistency and clarity.
  • Sorting and filtering data within a Date Hierarchy: You can sort and filter data within the hierarchy to focus on specific time periods or trends.

Conclusion

In this blog post, we explored the differences between Date and Date Hierarchy in Power BI and how to effectively use them in your reports. This feature allows you can create powerful and insightful visuals that help drive data-driven decision-making.


FAQ

Q. What is Date Hierarchy in Power BI?

A. Date Hierarchy in Power BI is a feature that allows users to group dates by different levels, such as year, quarter, month, day, and even hours. This feature makes it easy to perform time-based analysis and visualization in Power BI reports.

Q. How can I access the Date Hierarchy in Power BI?

A. You can access the Date Hierarchy feature in Power BI in three ways: the Date table, the Modeling tab in the ribbon, or the data field properties of the visual.

Q. Which visuals work best with Date Hierarchy in Power BI?

Date Hierarchy works well with visuals that support hierarchical data, such as bar charts, column charts, or treemaps.

Q. How can I customize Date Hierarchy visuals in Power BI?

You can customize Date Hierarchy visuals in Power BI by formatting display options for different hierarchy levels and sorting and filtering data within the hierarchy to focus on specific time periods or trends.

Q. Is it possible to drill down and up in Date Hierarchy visuals?

Yes, you can use the drill-down and up functionality to navigate through different levels of the hierarchy, allowing for in-depth data exploration.


Tutorial

As with most data analysis tasks, working with time can be the most challenging detail to get correct.  Power BI has built-in functionality that will display a drillable hierarchy of year, quarter, month, and days when a column, formatted as a date, is dropped onto a visualization.  This feature can be handy in the proper visualization, as illustrated in the example below.

Drag & Drop A Data Value – – A Date Hierarchy Shows Up

If you create a table and drag a field formatted as a date, you do not get a single date but the hierarchy pictured below.  This creates a built-in way to drill down through the year on various graphs and tables.  

A date visualization allows you to provide different views of your data over time.  Depending on your needs, you can remove a level from the list by selecting the ‘X’ next to the level.

However, this behavior is the default. If you want to show the actual date field, you must click on the down arrow next to the date field, as shown below.  This allows changing the selection from the Date Hierarchy to the field that contains your Date Values.

The two charts below illustrate the two different options. The top chart, using Date Hierarchy, allows you to use the visualization’s drill-down functionality, and the bottom graph, showing Date Value, shows the values as they appear in the data table.  With three years selected, the bottom graph shows all the months over those years, whereas the top graph, using the hierarchy, totals all the years into each month.

FinalVersion2

You need to add a filter or slicer to ensure that you look at the data for only one year and design your visualization to ensure your end user does not make a mistake or get lost.

The built-in date hierarchy feature allows the adding time drill functionality; you need to be careful when showing and selecting the correct values in your visuals.  The final version is shown below, with the months showing only the totals for the selected year.  The slicer is set so only one value at a time can be selected.

Final sample Power BI report with Month totals
Final Version with Month Hierarchy.

Related

Steve Young

With over 34 years of experience in the data and technology industry, the last 16 with Microsoft, I have had the opportunity to work in various capacities, contributing to my knowledge and expertise in Data Engineering, Power BI, and Data Visualization.

Facebook Twitter Instagram YouTube Linkedin Pinterest

Post navigation

Previous Previous
Why use Visualizations and Infographics With Data Story Telling
NextContinue
Accomplishment Logs Track Your Success for Employee Reviews
Search

Categories

  • Business Intelligence (0)
    • Self-Service BI (3)
  • Data Analytics (0)
    • Azure Data & Analytics (6)
    • Data Analysis With Python (3)
    • Data Storytelling (2)
  • Data Architecture (0)
    • Data Governance (5)
    • Data Modeling (2)
    • Power BI Solution Architecture (11)
  • Data Visualization (0)
    • Power BI Data Visualization Framework (2)
    • Power BI Data Visualizations (7)
  • Technical Education and Training (0)
    • AI in Education Content Creation (1)
    • Creating Training Materials (3)
    • Learning Paths in Tech (2)
    • Visual Learning (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • Powered by WordPress.com.

Disclaimer: The views expressed are my own. I offer no guarantees for the accuracy of the information shared, and is for educational purposes only.

All non-original photography is sourced and licensed from my account on PEXELS,  STORYBLOCKS, iStockPhoto, and Pixabay. Please use our Contact Page if you have a question.

The information provided on this blog is for educational purposes only. Steve Young is not responsible for any errors or omissions or for any actions taken based on the information provided on this blog.

© 2023 5MinuteBI

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.Accept Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT
  • Home
  • Blog
  • About
  • Contact