Power BI Line Chart Date Hierarchy [With Examples]

Do you want to learn how to add a date hierarchy with an X-axis in the Power BI line chart? In this Power BI tutorial, we will explore adding a date hierarchy to the X-axis in the line chart.

Additionally, we will cover how to drill down label hierarchy in the Power BI line chart. Furthermore, we will discuss creating a continuous date hierarchy on the X-axis of the Power BI line chart.

Here are the topics we are going to cover:

  • Power BI Line chart with X-axis having date hierarchy
  • Date hierarchy drill-down labels in Power BI Line chart.
  • Continuous Date hierarchy on the x-axis of the Power BI line chart

Power BI Line chart with X-axis having date hierarchy

Here, we will see how to create a Line chart with an x-axis having a date hierarchy in Power BI.

For example, we will use the Financial sample data to create a line chart in Power BI.

We will use the Gross sales and date column from the Excel sheet to display Gross sales based on the date hierarchy. The initial step of the date hierarchy is the Year, so the Power BI line chart will display sales based on the Year.

Line chart with X-axis having date hierarchy
  • Open Power BI Desktop, click on Excel Workbook, or click on Get data -> select Excel Workbook.
Microsoft power bi line chart date hierarchy
  • Then select the Excel sheet from your local system; in the Navigator window, choose the table, click the Load button, load the data to Power BI Desktop.
Line chart with X-axis having date hierarchy Power BI
  • Now, select the Line chart from the visualization pane.
  • You can see the blank Line chart in the Power BI Desktop canvas.
Line chart with X-axis having date hierarchy in Power BI
  • Next, we will add the data to the Line chart in Power BI, for this, follow the steps:
    • Drag and drop the Gross sales column to the Y-axis field.
    • In the X-axis field, drag and drop the Date column.
  • Now you can see the Line chart displays Gross Sales based on Year, which is the first hierarchy for the date hierarchy.
Power BI line chart date hierarchy

To expand down each level of the hierarchy, we will use the drill drown feature, which we are going to cover in the next section.

See also  Power Automate Send Email Based on Form Response

Date hierarchy drill-down labels in Power BI Line chart

Here we will see how to use drill down in Power BI line chart Date hierarchy.

In the above example, we have used data hierarchy in the Line chart. But it is displaying the sales based on year, how, we can drill down each hierarchy to reflect the gross sales in each label of the date hierarchy [ Year> Quater>Month> Day]?

There are two ways we can use the drill-down feature in the Power BI Line chart

  1. Drill down one field at once
  2. Expand feature

Now, let’s see how to drill down labels in the Power BI Line chart date hierarchy.

Drill down one field at once

The Line chart will initially display the gross sales based on the Year. Subsequently, we can drill down to view the gross sales based on individual Month.

  • In the above example of the Power BI line chart, you can see all the drill-down options on the top of the line chart.
Power BI line chart date hierarchy drill down
  • By clicking on a single down arrow ↓ you can enable the drill-down feature in the Power BI Line chart.
  • Then, by clicking on the double down arrow ⇊, you can drill into each level of hierarchy [year> quarter> month> day]
  • The below Power BI line chart is drilled down to Month, so it shows gross sales based on Month
Date hierarchy drill-down labels in Power BI Line chart.

Expand feature

In the previous section, we saw how we could go to the next level of date hierarchy in the Power BI Line chart. That means if the default view is the Sum of gross sales by Year -> then we go to the next level by using drill down i.e. Sum of gross sales by Month.

See also  How to Add a Time Picker in the Power Apps Canvas App?

In this section, we will see how to expand all down one level in the date hierarchy in a Power BI Line chart. That means if the default view is the Sum of gross sales by Year -> then we will drill down to the Sum of gross sales by Month, quarter, and year.

Now, let’s see how to use the drill-down expanded feature in the Power BI Line chart date hierarchy.

  • We will use the same Line chart, so click on the drill-up button [2 times], and it will go back to the default level.
Date hierarchy drill-down labels in Power BI Line chart
  • To expand all down to one level of hierarchy, click on the expand-down icon.
  • Now, the Line chart displays the gross sales based on Year, quarter, and Month.
Date hierarchy drill down labels in Power BI Line chart

This is how to drill down labels of the Date hierarchy in the Power BI Line chart.

Continuous date hierarchy on the x-axis of the Power BI line chart

Here, we will see how to show continuous date hierarchy in the Power BI line chart.

For example, we will use the same financial sample data, here we will create a line chart that will show profit based on Year and month, like below screenshot below.

You can see the X-axis date hierarchy is not continuous, which means you can see after Sep 2013, it is showing data for Nov 2013 and Oct 2013 is missing.

So, this is not a continuous date hierarchy in the X-axis. To solve this issue, we need to change the X-axis type from continuous to categorical.

Continuous date hierarchy on the x-axis of the Power BI line chart

For this, follow the below steps to achieve continuous date hierarchy on the x-axis of the Power BI line chart.

  • In Power BI Desktop, to create a line chart, from the visualization pane, select the Line chart.
  • Now you can see the blank line chart is added to the Power BI Canvas.
How to create Continuous date hierarchy on the x-axis of the line chart in Power BI
  • To add the data to the Line chart in Power BI, for this, follow the steps:
    • Drag and drop the Profit column to the Y-axis field.
    • In the X-axis field, drag and drop the Date column.
  • Next, remove the Quater from the date hierarchy by clicking on the cross button ×.
  • Then click on the drill-down icon [Expanded feature] to drill down to Year-Month.
  • Now you can see the Power BI line chart shows profit by Year and month.
Line chart with X Axis having Date Hierarchy loses the continuous on workspace
  • To make a continuous X-axis having date hierarchy in a line chart, click on the Format pane.
  • Then expand the X-axis section; select the type as Categorical.
Line chart with X Axis having Date Hierarchy loses the continuous

Now you can see the continuous date hierarchy on the x-axis of the Power BI line chart

Continuous date hierarchy on the x-axis of the line chart in Power BI

This is how to add Continuous date hierarchy on the x-axis of the line chart in Power BI

See also  Power bi row level security tutorial

Conclusion

In this Power BI tutorial, we saw how to work with date hierarchy in the Power BI line chart.

Also, we have covered the below topics. These are:

  • Power BI Line chart with X-axis having date hierarchy
  • Date hierarchy drill-down labels in Power BI Line chart.
  • Continuous date hierarchy on the x-axis of the Power BI line chart

You may also like:

>