Using a Date Hierarchy Date Fields are a little different to other fields in a Power BI report, they are by default hierarchical.

That means even though they're just one column of data, they're actually broken down into several layers and there are four layers available by default.

So our column of dates can be grouped by year, by quarter, by month, or by day.

Date hierarchy is really useful in visuals because we can take one element of that deteriority. So for example a year, or a quarter, and we can use it individually in a visual, for instance in a slicer we can have a filter by year, or a filter by quarter, or in a column chart we could group our data by month.

Let's do a workout and see how to use the date hierarchy in Power BI.

So I'm just going to get an Excel workbook here.

and I'll take module five lesson 1A.

And from that workbook, I'll select the credit transactions worksheet and click on load, you can see here before I click and load I've got one column called transaction date which contains a list of dates.

I'm going to make use of the date hierarchy in my report, if I just go to the credit transactions, we can see here a little calendar icon beside the dart field. That indicates that it's a date data type which means it can be hierarchical and because it's hierarchical I have an arrow beside it and if I expand that and then expand the entire hierarchy, I will see the four different elements of the hierarchy now when I use a date field if I just select the date as normal just by ticking on it.

And then select say amount, to have the total amount for each date. I'm just going to change it here to column chart. We see this more clearly here and we can see how by default it has grouped the data using the first layer, the top layer of the hierarchy. I have the total amount for each year, I've three years worth of data here.

So that's great, I can keep that but what I can also do is I can then change the hierarchy. If I look over in the visualizations pane, I'll see here each of the different layers available and if I open up over to the right hand side, I can see by default it's choosing all of them so I can X them out here or I can just untick them over here, I'm just going to deselect year remove it deselect quarter. And now I have a completely different chart or I'm looking at the total amount for each month. I do have to be careful because I have three years worth of data. So when I look at the total for January that total amount is for the three years worth, but just the month of January in each year. It's maybe not so relevant to look at it that way. What I'll do is make a slight adjustment to my page I'm just going to click on to the canvas.

And I'm going to add in another visual gonna add in a slicer. I'll click on that and I'm going to add a field to my slicer. I'm going to use one of the layers from the date hierarchy, rather than select the entire date, I'm just going to go through the hierarchy and pick our year. Let's just click on that. Now we can see here it's use the year, but it's used in what's known as a timeline so I can keep that if I want and I can just change and by zooming in there to a particular area so I can focus in on a particular part of the data. I like 2021 and do it that way. You can see my visuals automatically changing there. Now I'm just going to go up to the arrow here. Just click into this little arrow and I'm going to take it back to a list and in a list it will just be the usual type of slicer there where we can just select the year that we want and again the column updates. Now just make that a little bit smaller click on the canvas and I'll add in one more slicer and in this one I'm going to use the quarter part of the hierarchy and this defaults to list and I'll keep it at that. So now I can really explore my data, i'll select 2021 i'll select Quarter Two and it brings the column chart just down to those three months.