Original subtitles

In this next lesson.

What I want to do is just spend a bit of time talking about dates and how we work with them in Power

BI.

So I'm just going to remove these visualizations from the previous lesson.

So let's start off just with a simple table again.

And let's move that over there.

And what you'll notice is that when you brought your data into Power BI, it should have recognized

that ordered data is actually a data field.

And what it will do is it will give it a calendar.

You'll see a little calendar icon next to it.

Also, if you click on it, you should see then that it's a data type of date.

And also you could change the format that this is displayed in if you want to, using your column tools.

So hopefully that happened when you brought the data in that the audit data was actually seen as a data

field.

Now, if you move that into a table, what you'll actually see happens is that it actually creates a

hierarchy.

So you'll see that it automatically breaks this down into my year, my quota, my month, my day.

So showing there's four different fields now in my table.

If you want to see your sales value, you can add that into your table.

Here we go.

Now we've got each of those four different options for your hierarchy, for your date.

What you can also see, though, is if you go to your dropdown, you can change this to be just a traditional

date.

So if you just said order date, now you'll see that it changes to one field and you've got a traditional

date that you can use.

So this hierarchy becomes very important when you're working with dates within Power BI because it gives

you different ways of being able to aggregate your information and understand your data information.

So over here we can see by each day what happened with ourselves.

But what happened if we want to see for a specific year or for a specific month.

So let's go back to looking at our data hierarchy.

So if we go back and we actually pick our date hierarchy, so I'm just going to go down, you can see

it's at the bottom here.

So date hierarchy, it goes back to my year called Month day.

Now you will see that there's a little X against each of these options.

So if I don't want to show the day, I can actually remove it.

Now you'll see that this is summarized by year, quarter, a month, and I'm now seeing the total sales

for the summarization.

So what I'm going to do is I'm actually going to remove the quota just to make this a little bit easier,

because now we can see that we have a year and a month and we have our sales.

So that's quite useful to be able to see your total sales by month.

And you can see as well that this is incorrect chronological order, which is obviously quite convenient.

Some systems don't get this right.

It actually does it by alphabetical for your month and causes a bit of a problem.

But as you can see, this is in your correct chronological order.

Again, if I was to remove the month here, then you would see, then we would just then have the totals

for our year.

If you wanted to change this, you would have to now replace the actual field because we can't just

add back.

So if I want to take that out, I can just drop all the date back in again.

And let's say we did actually want to see what's happening by year end quarter.

Now I'd just go back and then just remove the day, remove the month, and now I could see by year on

quarter what was happening by my sales.

So really, really useful.

So what I want to do is actually let's just move that across to one side and you're going to delete

that one and we're going to look now at a matrix and what can happen with the matrix.

So we're going to bring up a matrix.

And as we've seen with the matrix, we're going to get the ability to work with our rows and columns.

So let's say we drop an ordered data into our rows and let's say we drop an order date now into our

columns.

So you'll see that a lot of information would be displayed.

And we've also got our cells.

So we've got our sales and currently it is showing me by each of the years now it's not showing me currently

the columns because I have too much data.

So let's see with the columns, if we can bring this down a little bit, let's say we want to show the

actual month.

So I'm going to take out of here the day, I'm going to take out the quarter and I'm going to take out

the year.

So now we just got the month.

Now you can see that it can show the amount of data and automatically what it's done.

Let's see if we can widen this a little bit.

There we go.

We now have a view that is showing us by year and by month what has happened.

So this is really your traditional management report that we often put together in Excel spreadsheets

where we want to see by year and month exactly what's happened and what I could do as well.

You can see that I do have the drill downs on here, but I probably wouldn't need them.

So again, I can just remove these fields that I don't need and we just keep for example, here and

there we go.

We've got that.

What you can do as well is you could see this from other dimensions.

So you may want to see what happens by different regions.

Let's actually use regions.

If you pop up your regions and you'll see that you've got Asia, Europe, North America, you've got

each of your months.

And as we see in if we use this drill down, you will now actually drill down for each of your years

so you can create a sort of complexity as you would want to by using these different options.

So as you can see, the data can work as a hierarchy or it can also just work with ordered data itself.

So we're going to conclude the lesson there.

I will see you in the next one.