Power BI date hierarchy [With 21 real examples]

In this Power BI Tutorial, we will learn all about date hierarchy in Power BI with a few examples listed below:

• Power bi date hierarchy
• Power bi date hierarchy add week
• Power bi date hierarchy by week
• Power bi date hierarchy shows month and year
• Power bi date hierarchy month
• Power bi date hierarchy month order
• Power bi date hierarchy sort by month
• Power bi date hierarchy short month name
• Power bi date hierarchy showing future dates
• Power bi date hierarchy shows an only year
• Power bi date hierarchy remove
• Power bi date hierarchy change name
• Power bi date hierarchy quarter
• Power bi date hierarchy weekday
• Power bi date hierarchy with time
• Power bi date hierarchy drill down
• Power bi date hierarchy day of week
• Power bi date hierarchy direct query
• Power bi date hierarchy fiscal year
• Power bi date hierarchy minutes
• Power bi date hierarchy missing

Power BI date hierarchy

By default, Power BI automatically identifies Date data and arranges it in your hierarchy. Useful for the showing year, quarter, month, and day hierarchies. The Date hierarchy in Power BI is a way to structure your data table to conveniently drill down and drill updates based on various grouped time slices.

The Power BI Date hierarchy looks like this:

Read Add Title, Image, and Video to Power BI Dashboard

Power BI date hierarchy add week

Here we will see how to add a week to the date hierarchy in Power BI.

As Power BI automatically creates a date hierarchy that has Year, Quarter, Month, and Day. So in between Month and Day, we want to add Week num. For this, we need to create our own custom Date hierarchy. To create a custom date hierarchy, follow the below steps:

1. First, we will create our own date table, so create a new table with the below formula:

Calendar = ADDCOLUMNS (
CALENDARAUTO(),
"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" ),
"Day", Day([Date]),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

2. Secondly, hover over the Year column -> click on the More icon (…) -> Click on Create hierarchy option in the Calendar table.

Now a hierarchy gets created with the Year column like the below Screenshot. Next, we will add Quarter, Month, Weeknum, and day to the hierarchy.

3. Next hover over the Quarter column -> and then click on the More icon(…) -> select Add to the Hierarchy -> Year hierarchy.

Similarly, add the MonthNameLong column, DayOfWeekNumber column, and Day column to the hierarchy. Then you can see the custom Date hierarchy with week num:

This is an example of Power bi date hierarchy add week.

Read Power BI Error: This content isn’t available

Power bi date hierarchy by week

Here we will see an example of Power BI date hierarchy by week.

As we know the date hierarchy automatically created in Power BI does not contain week numbers, so we can create a custom date hierarchy as we did in the topic ‘power bi date hierarchy add week’ or we can use the date hierarchy in our visuals by adding Week column. In visuals, by drilling up and down we can go to each stage of the date hierarchy.

We will use the Date table we have created for this topic ‘ power bi date hierarchy add week’. And also we will use the Financial table 2021-2023. To use bot the table visually create a relationship between the date columns between both the table.

• In Power BI to use the date hierarchy with week, select a stacked column chart -> then in the Value -> Sales from the Financial table in the Field pane.
• Then in the Axis field -> First add the Date column from the Date table -> delete the day column -> Drag the DayOfWeekNum column into the Axis, -> then drag the date column into the Axis->delete Year, Month and Quarter.

• Next click on the drill-down (double down arrow) icon at the top of the visual. To see the Sales based on the week, go to the 4 levels of the hierarchy by clicking on the double drill-down arrow.

This is an example of a Power bi date hierarchy by week.

Read Power BI Group By Examples

Power BI date hierarchy shows month and year

Here we will see how to show month and year at the same time in the hierarchy in Power BI.

We will create a Stacked column chart, which will show Sales based on Month and year.

• In Power BI, select the Stacked column chart from the Visualization pane, then drag the Date hierarchy(Year and Month) from the Field pane into Axis and Sales into Value Field in the Visualization pane.
• In Power BI click on the drill-down icon(tree icon), and you can see the Year Month on the axis.

This is an example of the Power bi date hierarchy showing month and year.

Read Power BI nan error

Power BI date hierarchy month

Here we will see how to use month from date hierarchy in Power BI visual.

For example, we will create a Stacked column chart that will show the Sales based on Month.

• In Power BI, create a stacked column chart -> drag the Date column from the field pan into the Axis, and then drag the Sales from the Field pane into the Value field in the Visualization pane

Then click on the drill down icon(double down arrow), to the 3 rd level of the hierarchy, where you can see the Month column in the Axis.

This is an example of Power bi date hierarchy month.

Read Power BI report using People Picker Field

Power BI date hierarchy month order

Here we will see how to sort month order as descending order in Power BI.

In Power BI, we have a Stacked column chart having Sales by Month, and we want to order the Month in descending order.

To sort the order of the Month in descending, select the visual -> click on the More icon(…) -> Select Sort axis -> Month – Sort descending.

This is an example of Power bi date hierarchy month order.

Read Data Labels in Power BI

Power BI date hierarchy sort by month

Here we will see the date hierarchy sorted by month in Power BI Desktop.

In Power BI, go to the Modeling tab -> select the Month column -> Select Sort by column -> Month number.

Now your date hierarchy also gets sorted by month in Power BI. You can see I have created the stacked column chart with a date hierarchy, all the dates are sorted in ascending order.

This is an example of a Power bi date hierarchy sort by month.

Read Power bi table visualization

Power bi date hierarchy short month name

Here we will see how to create a date hierarchy with a short month name in Power BI.

For example, we will use 3 character month abbreviation in the date hierarchy, for this we need to create our own date hierarchy.

So here we will use the Calculated Date table ( which we have created in the second section) , from this table we will use the MonthNameShort column in which Month is formated as ‘mmm’.

• In Power BI, hover over the Year column -> click on the More icon(…) -> click on the Create hierarchy.

• Next, we will add the MonthNameShort column to the hierarchy, so hover over the MonthNameShort column, -> click on the More icon (…) -> Add to hierarchy -> Short Month.

Now you can see the Date hierarchy gets created with the short name. You can see the short month name in the Stacked column chart visual.

This is an example of a Power bi date hierarchy short month name.

Read Subtraction in Power bi using DAX

Power bi date hierarchy is not in order

Here we will see how to change the order of date hierarchy in Power BI.

To change the order of date hierarchy in the visual, follow the below steps.

• In Power BI, we have a Stacked column chart having Sales based on Year and Month ( date hierarchy). You can see Year and month in the Axis by drilling down the next hierarchy level, which is not arranged in order.

• So to arrange it in ascending order, click on the More icon at the top of the visual. Then select the Sort axis -> select Year MonthNameShort -> Sort ascending . Now the date gets Sorted in ascending order based on the order of Sales for each month in the Year.

This is an example of a Power bi date hierarchy is not in order.

Read DAX Filter function

Power bi date hierarchy showing future dates

Here we will see how to filter future dates shown by the date hierarchy in the visual in Power BI.

To hide the future date from the date hierarchy used in the below Stacked column chart, follow the below steps:

• To filter the future date, drag the date column to the ‘Add data fields here’ under Filter on this visual in the Filter pane.
• Then select the Filter type as the Relative date, then set Show item when the value is in the last 2 years, and check the box Include today. Click on Apply Filter.

Now you can see the future date is filtered out, in this case, the current date is 5/19/2022, so it will show you the date from Sept 2021 to May 2022.

This is an example of Power bi date hierarchy showing future dates.

Read Get Current Month Sales Report using Power BI Measure

Power BI date hierarchy shows an only year

Here in KPI visual, the date hierarchy shows an only year in Power BI

Because the KPI visual in power Bi does not support date hierarchy. So if you want to show the date click on the down arrow and select Date.

This is an example of the Power bi date hierarchy showing only year.

Read Export Power BI reports to PDF

Power BI date hierarchy remove

Here we will see how to remove the date hierarchy in Power BI.

To remove the below default date hierarchy from the current file in the Power BI follow the below steps

• In Power BI, click on the File tab -> Options and Settings -> Options -> click on Data load in the Current File section -> Then turn off the ‘Auto Date/Time’ under Time Intelligence.

• Now you can see there is no date hierarchy available in the Current File table.

Read Export Power BI Reports to Excel

This is an example of Power bi date hierarchy remove.

Power BI date hierarchy change name

Here we will see how to change the Field name in the Date Hierarchy in Power BI.

In Power BI we cannot change the field name from the default date hierarchy, but we can create a custom date hierarchy, where we can rename the field in the hierarchy.

For example, I have created a custom date hierarchy, from this hierarchy, I want to rename the field DayOfWeekNumber to WeekNumber.

To change the name of the field in the hierarchy, hover over the field -> click on the More icon (…) -> click on Rename.

• Now rename the field in the hierarchy to WeekNumber in Power BI.

This is an example of a Power bi date hierarchy change name.

Read Power BI Change Data Type of a Column

Power BI date hierarchy quarter

Here we will see how to add a quarter column to the custom date hierarchy in Power BI.

So here I have created a Custom date hierarchy, having one column i.e. Year column. So, in this hierarchy, we will add the Quater field.

To add the Quarter field to the date hierarchy, select the Quarter field in the table -> click on the More icon -> then click on Add to Hierarchy -> click on Year hierarchy.

Now you can see the Quarter field get added to the hierarchy in Power Bi.

Read Remove blank from Power bi slicer

This is an example of the Power bi date hierarchy quarter.

Power bi date hierarchy with time

Here we will see how to create a date hierarchy with time in Power BI.

In Power BI, I have the separate date and Time columns in the data table, so the default date hierarchy doesn’t contain any time field. Also, we cannot add the Time column to the default date hierarchy. For this, we will create a custom date hierarchy with time, I have the below date hierarchy, and we will append the Time column.

Now to add the Time column to the custom date hierarchy in Power BI, select the Time column -> click on the More icon (..) -> Add to hierarchy -> Year hierarchy.

Now you can see the time column get added to the date hierarchy and you can show the date hierarchy with time in the visual.

This is an example of Power bi date hierarchy with time.

Read Difference between USERNAME() and USERPRINCIPALNAME() in Power BI Dax

Power bi date hierarchy drill down

Here we will see how to drill down through the date hierarchy in Power BI

So we will create a Stacked column chart and we will use Sales based on Date ( Date hierarchy). Then we will drill down the level of hierarchy.

There are two ways we can drill down, the first way is inline drill down by clicking on the double down arrow, we can drill down each level of the date hierarchy( Year -> Quater ->Month -> Day). And the second way of drill-down is Tree drill down by clicking on the tree icon, we can expand to the next level of the hierarchy (Year -> Year Quater->Year Quater Month -> Year Quater Month Day).

Now we will see the first way of drill-down, by going one level down the date hierarchy (Year -> Quater) in the Stacked column chart.

• For this select, the Stacked column chart, add the date hierarchy column -> axis, and Sales column to the Value.
• Then click on the double arrow icon, and you can see the date hierarchy goes to the next level of hierarchy.

Next, we will see the second way to drill down by expanding all down one level of the hierarchy i.e. Year -> Year Quater.

• For this select, the Stacked column chart, add the date hierarchy column -> axis, and Sales column to the Value.
• Then click on the Tree arrow icon, and you can see the date hierarchy expanded to the next level hierarchy.

This is an example of a Power bi-date hierarchy drill down.

Read How to create and use Power BI Bookmarks

Power bi date hierarchy direct query

In Power Bi date hierarchy is not available for the Direct Query mode, so we can create a custom date hierarchy. To create the custom date hierarchy follow this topic ‘power bi date hierarchy week’

Power bi date hierarchy fiscal year

Here we will see How to create a Date hierarchy for the Financial year or Fiscal year in Power BI.

To create a date hierarchy for the Financial year, we need to calculate, the financial year, financial quarter, and financial month.

• In this case, the fiscal year is starting on October 1 of 2022, so the financial year is in FY22. To get the financial year create a Calculated column by using the below formula:

FinYear = if(MONTH(Sheet1[Date])>=10,YEAR(Sheet1[Date])+1,YEAR(Sheet1[Date]))

• In the linear calendar, Oct will be the fourth quarter, so as to calculate the economic Years quarter, search for the [date], QUARTER and if that is identical to or greater than 4, we take the QUARTER of the date and subtract 3, else we use the QUARTER of the date and upload 1. So, to calculate the fiscal quarter, create a calculated column by using the below formula:

FinQtr = if(QUARTER(Sheet1[Date])>=4,QUARTER(Sheet1[Date])-3,QUARTER(Sheet1[Date])+1)

• In the fiscal year the number of months, look for [date], MONTH, and if this is greater than or equal to the month of the first fiscal year (October 10), take minus 9 from the date MONTH. Otherwise, add 3 using the month of the date. So, to calculate the financial month, create a calculated column by using the below formula.

FinMonth = if(MONTH(Sheet1[Date])>=10,MONTH(Sheet1[Date])-9,MONTH(Sheet1[Date])+3)

• Now you can create a date hierarchy for the Financial year, in the Field pane -> click on the FinYear field -> click on the More icon -> Create hierarchy option.

• Next, we will add the FinQtr and FinMonth fields to the hierarchy, by selecting the column -> clicking on the More icon -> then clicking on Add to hierarchy -> selecting the FinYear hierarchy.

Now you can see the Financial Year date hierarchy in the data table in Power BI.

This is an example of the Power bi date hierarchy fiscal year.

Read Power bi free vs pro vs premium

Power bi date hierarchy minutes

Here we will see how to add minutes to the Date hierarchy in Power BI.

So, the default date hierarchy does not contain the Time factor, also we cannot add to it. For this, we need to create a custom hierarchy. So, I have created a date hierarchy like the one below screenshot, then we will add the hour and minute column to it.

To add the hour and minutes to the date hierarchy, select the field -> click on the More icon(…) -> Add to hierarchy -> Year hierarchy 2.

Then you can see the hour and minutes fields added to the date hierarchy in Power BI.

This is an example of Power bi date hierarchy minutes.

Read What is the difference between calculated column and measure in Power BI

Power BI date hierarchy missing

Here we will see why the date hierarchy is missing in Power BI.

Do you often see calendar icons next to date fields? Yes, this is the Power BI Date hierarchy. It is easy to understand because it is decorated with a calendar icon.

You are using the calendar icon a lot, but you are may not clear about the meaning of the icon. Here is the reason:

The calendar icon refers to the Calendar date field with a built-in date table. This option is useful when building ad hoc models, and exploring and profiling data. However, you may need a date hierarchy to plot the chart and there may not be a calendar icon next to the field. Why did the date hierarchy disappear? This is a shame and we cannot move on to the next step. I need a date hierarchy to help me.

It is clear that the date column type in the model is already a date type. Why is there still no Date hierarchy in the Table? If you have undergone such a problem, then here are some of the reasons:

Enable Auto date/Time

First, you need to know that the date hierarchy is due to the Automatic date and time feature.
Automatic date/time is a data loading option in PowerBI Desktop.

The objective of this option is to support useful time intelligence reports based on the date columns loaded in the model. Specifically, it allows report authors to use the data model to filter, group, and Drill-down using calendar periods (years, quarters, months, days). If you have not checked this option, you must check this option first.

In Power BI Desktop, click on File> Options and Settings> Options, then select the Global or Current File page. On either side, check the options are in the Time Intelligence section.

Automatic date/time can be configured globally or for the current file. Global options apply to new Power BI Desktop files and can be turned on or off at any time. By default, if you reinstall Power BI Desktop, both options will be enabled.

The current file option can be turned on or off at any time. When on, an automatic date/time is created in the table. When disabled, all automatic date/time tables will be removed from the model.

Note: Please note that Switching off the current file option will remove the automatic date/time from the table. Be sure to fix broken report filters or visuals configured to use them.

Why still no date hierarchy?

When you switch on this option and complete the requirements, you still cant see the date hierarchy. Why? Lets see the example below.

There are two tables in Power BI, Date and Table. The Date field is a date type, the Key field is an integer type, and the other fields are a text type. It will be as follows:

This is the case for Date hierarchies, there is no relationship between the tables.

The next step is to show a relationship between the two tables of dates.

In Figure 2, many aspects of the many-to-one relationship have lost the date hierarchy, while one aspect retains the hierarchy.

If the relationship between dates is one-to-one or many-to-many, why are there such results in Figures 1 and 3? How does Power BI determine who really is in a one-to-many relationship? Power BI also removes the date hierarchy for this field.

Therefore, when establishing relationships between fields, the first selected column is considered “many” by Power BI, and the second selected column is considered “one” by Power BI

I also noticed that the other Date column that is not directly related holds the date hierarchy. Therefore, if you use the DateAsInteger column (integer type) to establish a relationship, the DateType column maintains a date hierarchy.

And when a Date type field is related to another date type field, still Power BI removes the hierarchy of the fields of Many sides in one Many relationships.

Based on what we have seen above, another important condition that affects the Date hierarchy: The Field is not the many sides of a model relationship.

If the relationship between tables is one-to-many, Power BI will respect your decision and remove the ‘Many’ side of the date hierarchy.

And if the relationship between tables is one-to-many or one-to-one, Power BI will determine which one is truly the “many” sides based on the order of selection.

Regarding the date hierarchy, what we need to be aware of:

1. The column data type is date or date/time
2. The column isn’t the “many” side of a model relationship
3. Enable automatic date and time
4. The table storage mode is Import

This is the possible reason to overcome the missing Date hierarchy in Power BI.

You may like the following Power BI tutorials:

• How to use weekday function power bi with example
• Contact your admin to enable embed code creation Power bi
• Power BI convert hours to minutes
• Power BI convert yyyymmdd to date
• Get Month Name from Month Number in Power BI
• How to use Power BI sync slicers
• Power BI Calculated Column Example

In this Power BI Tutorial, we learned about the Power BI Date hierarchy with examples and we use the data table i.e. ‘Financial table 2021-2023’, which you can download from this link. The examples are listed below:

• Power bi date hierarchy
• Power bi date hierarchy add week
• Power bi date hierarchy by week
• Power bi date hierarchy shows month and year
• Power bi date hierarchy month
• Power bi date hierarchy month order
• Power bi date hierarchy sort by month
• Power bi date hierarchy short month name
• Power bi date hierarchy showing future dates
• Power bi date hierarchy shows an only year
• Power bi date hierarchy remove
• Power bi date hierarchy change name
• Power bi date hierarchy quarter
• Power bi date hierarchy weekday
• Power bi date hierarchy with time
• Power bi date hierarchy drill down
• Power bi date hierarchy day of week
• Power bi date hierarchy direct query
• Power bi date hierarchy fiscal year
• Power bi date hierarchy minutes
• Power bi date hierarchy missing