Original subtitles

Welcome to this lesson.

In this lesson, we're going to be looking at our date field in a lot more detail.

We're going to be looking at how we can use our calculate columns.

So I'm going to go back to my report.

I cleared those previous tables we were using, but let's say we go back to creating a new table and

make a bit bigger and we've got a sales date.

If we drop the sales date into the table, you'll see now that we've got our sales date, which is made

up of our hierarchy of year, quarter, month and day.

So it now shows those within the table.

But what would happen if we just want to show the year by itself?

So as we know, we can remove these, but that's quite a lot of hard work.

And also it doesn't always give us the amount of flexibility that we might want when we're creating

our reports.

What we might want is just year as its own field.

Okay, So we're going to have a look.

We're going to see how we can create that.

So let's go back to our data and we're going to add a new column again.

So in this case, I can just go up to my table tools, go to new column.

We're going to select that.

Our formula bar will come up, hopefully getting used to this.

Now, the same rules apply.

We must always have a field name.

So in this case we're going to call it year.

It's perfectly acceptable to call it a function name so we can call it.

Yeah, just make sure it's not the name of a field that already exists.

Then the system will have a problem with it.

When I say year equals and in this case we're going to now say put a Y into the system.

And what you'll see is that return all of the functions that begin with the Y, so very much like Excel.

So Power BI uses a formula language called Dex, and Dex is very similar to the Excel function language

in a number of aspects.

It is actually more powerful than a number of other aspects, but you'll tend to find that with your

Excel knowledge you can actually get up and going very quickly with Power BI and using the DAX formula

language.

So as I said, it works in a similar way.

We're going to use the year function, so we're going to select that and you'll see now that it actually

gives me an explanation that says, Yeah, what is the date we're going to be using?

It's also got an open parentheses like we would find in Excel.

So in this case we want to know what is the year of the sales date.

So again, I can use my left square bracket, I can open that, We can go down in our fields.

We see that we've got our sales date there.

We're going to select that.

So basically we're going to be now creating this formula which says the year field is going to equal

the year function of the sales date.

Okay, let's press enter on that.

And you can see now that new field has been created, which just has the year itself, whereas the sales

dates, I've got the full sales date in there.

Now if we go back into our report and create a new table, we'll now see that we've got our year field.

And you can see again, it's got the calculated column icon.

Drop that in there.

Now you can see straight away we do have a bit of a problem because it's the summing of the year, but

we'll fix that now.

So we're going to put our sales in there.

So we've got our sales.

Let's go back to the sum of the year.

And in this case, we're going to say we don't want to summarize this.

So you can see when we say done summarize, it's now showing me each individual value of my year, which

is exactly what I want.

And I've now got my sum of sales for each one as well.

So that's perfect.

That's exactly what I wanted.

However, in the future you may find that you don't want your year to sum at all, so you can select

the year, you can go to your column tools and you see in your default method of summarization that

you can change it from some and you just say, I don't want this to summarize.

This should now change the default that if we create a new table, hopefully this will work, create

a new table, and let's just drop a year in there that it should.

There we go.

It doesn't summarize.

So that's really helpful.

We've got those figures in there, so I'm going to delete that.

So let's go back.

Let's look at another example.

So let's say, for example, we wanted to know what is our month number.

So we've got a year, let's go and add our month number.

So I'm going to say new column and we're going to say month.

And going to say month equals.

And in this case, you'll see that there is actually a function called month, just like Excel.

Again, you'll see that when I typed in Mo N, you'll see a whole bunch of new functions that are available

in Dex that you may not know from Excel.

And as I say, really, please just go and research those.

You'll see there's quite a lot.

Now you'll see that the same syntax applies here.

We got a month function or we're entering.

Here is the month of which data.

In this case it is of the sales date.

So we're going to select sales date again, close our parentheses.

So there we go.

Month equals the month function of the sales date.

And again, when I press enter on this now, you'll see that we get a new month being created.

And there we go.

We've now got that information.

So as you can see, this one, September, that's number nine.

And again, we could go back to our report.

So we've got our existing report with our year.

Let's change this to a matrix.

And let's say that now in the column we wanted to use our month number and there we go.

Now, you've got a traditional management report that is now showing us all our month numbers going

across.

And we've got our years going down in our rows.

So there we go.

That's how we would create all month.

And again, you can do the same thing, just click a month.

Make sure in your summarization you would say you don't want it to summarize as the default, except

that we're going to look at a couple more examples.

So let's go back into our data.

Let's say we wanted to know what was the day of the month so we could go again.

New column.

And we're going to create a new field called Day.

I'm going to say de equals.

In this case, it's going to be the day function.

Open your parentheses square bracket.

Go down to your cells.

Date.

Select that.

Close your parentheses.

Enter.

And there we go.

Now you've got the day that you could use in your reports as well.

I'm going to create a couple more here.

And what we're going to look at is all weekday.

So often what you want to do is you may want to know what is the week day like?

Is it a monday?

Is it a Tuesday and Wednesday?

So we're going to use a function called equals weekday.

So let's go and create a new column again and you'll see weekday.

And we're going to say equals.

And in this case, just like Excel as well, you'll see that there is a function called equals weekday.

Also, if you're interested in the week number, you will see that this equals week num, just like

you've got it in Excel as well.

In this case let's just use equals weekday and again we're going to be using our sales data as the input

to this.

And he went to that.

Now what you get is you get a result showing you one through to seven.

Let's actually have a look in a report.

Let's delete this existing report.

Select that to use a new table.

So if we use weekday now, we dropped it in there again, it summed it.

So we're going to say you don't want to summarize.

You can actually change the default.

So don't summarize.

There we go.

So now we've actually got the different sales on each day of the week.

However, if you gave this report to somebody else, it may cause a bit of a problem because the question

would be is what is week day one?

Is it a Sunday?

Is it a monday?

Is a Tuesday.

Do we know is a7a Sunday?

We're not sure.

So what you might want to do is actually use the name of the actual weekday.

So at this point, I'm going to conclude this lesson.

We're going to continue this lesson into the next lesson, and we're going to be looking at how we can

create our weekday names.

We're going to look at month names, so we're going to look at some of the formatting and things that

we need to do around that.

But I will see you in the next lesson.