Power BI Date Hierarchy – Complete tutorial

in this Power BI Tutorial, we will learn about Power BI Date Hierarchy. Also, we will discuss:

• What is Hierarchy?
• Introduction to Power BI Date hierarchy.
• Date hierarchy benifits and examles.
• Power BI auto create date hierarchy
• Power BI create date hierarchy from date
• Power BI create custom date hierarchy
• Power BI create date hierarchy with week

What is hierarchy?

A hierarchy is a pyramid-like structure that organizes and ranked our data according to the power of importance.

In a hierarchy, every level has one higher and one lower neighbor.

Introducing to Power BI Date hierarchy

Microsoft provides a built-in function in Power BI known as Date hierarchy.

By default, Power BI detects and organizes the date data in the hierarchy automatically. It helps to display the hierarchy of year, quarter, month, and day.

The data hierarchy is a way of structuring our data table for comforting drilling down and up the data based on different grouped time slices.

A Power BI Date hierarchy looks like this:

Read How to create a table in Power bi dashboard

Date hierarchy benifits and examples

The data hierarchy features make the data displaying more convenient and intuitive. It is an effective approach to speed up the report data access and restrict users from overstretching reports straight away.

Examples of date hierarchy are:

• Year-month hierarchy
• Year-month-quarter hierarchy
• Year-month-week hierarchy

Power BI auto create date hierarchy

Power BI can create a date hierarchy automatically, by configuring auto date/time globally or for the current file.

The global option applies to the new Power BI Desktop file, and it can be turned on/off at any time. Similarly, the current file option can also be turned on/off at any time.

To configure these option on Power BI go to file > options and setting > options > data load(global/current file) > Time intelligence.

This is how we can configure the auto date/time to create a built-in hierarchy in Power BI. By checking this option, a date hierarchy will create automatically on the Power BI data model.

Read Power BI Aggregate Functions

Power BI Create date hierarchy from date

Now we will see how to create a date hierarchy from date in Power BI with a step-by-step guide.

Step-1:

For example, here we have sample data of a product’s financial report which we have loaded in Power BI Desktop.

On Power BI Desktop, go to model > date(right-click) > create hierarchy.

Step-2:

Then it will create a new date hierarchy on the data model. we can customize its name and other few options on the Properties field.

Step-3:

Now we will create a table visually using this date hierarchy on the report page. It will look like this:

For a better understanding, here we are using another column(i.e. segment). This is how to Create a date hierarchy from the date on Power BI.

Read Power bi measure subtract

Power BI create custom date hierarchy

We create different types of custom date hierarchies, such as:

• Year-Month hierarchy
• Year-Quarter-Month hierarchy
• Year-Month-Week hierarchy

Example-1: create Year-Month hierarchy

Step-1:

On Power BI Desktop, go to model > date(right-click) > create hierarchy. It will create a new data hierarchy.

Step-2:

On the properties pane, rename the Hierarchy name to Year-Month Hierarchy. On the hierarchy field, select the year and month column to customize the Hierarchy like this:

Step-3:

We can see a Year-Month hierarchy is created on the data model. To visualize this data hierarchy, here we use a table chart:

Like, Year-Month hierarchy we can create a Year-month-quarter hierarchy and Year-month-week hierarchy in the same process.

Read Power bi date filter

Power BI create date hierarchy with week

To create a date hierarchy with a week we have to follow these below steps.

Step-1:

Here we create a table by applying this code. It will create a table as Date having some column, such as year, month, a particular days (i.e. starting date to ending date), week, quarter, etc.

Date = 
ADDCOLUMNS (
CALENDAR (DATE(2017,1,1), DATE(2017,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

Step-2:

Now click on DayOfWeek > Create hierarchy. It will create a week hierarchy on the table.

Step-3:

Now, will add Date to this hierarchy to create a date hierarchy with week. For this click on the Date > Add to hierarchy > DayOfWeek Hierarchy.

We can see the date will add to the hierarchy.

Step-4:

Let’s use this hierarchy on the Power BI report. It will come like this:

You may like the following Power BI tutorials:

• Power BI Slicer between two dates
• Microsoft Power BI KPI Visual
• Power bi slicer multiple columns with examples
• Power bi slicer checkbox
• Microsoft Power BI Card
• Power BI Measure IF with Examples
• Power Bi Time Intelligence Function

Conclusion

From this Power BI Tutorial, we learned all about the Power BI Date hierarchy. Also, we discussed:

• What is Hierarchy?
• What is Power BI Date hierarchy?
• What are the benifits and examles of Power BI Date hierarchy?
• How to create date hierarchy automatically on Power BI?
• How to create date hierarchy from date on Power BI?
• How to create custom date hierarchy?
• How to create date hierarchy with week on Power BI?