All language subtitles for 7. Power BI Demo Working with Date & Time Tools

All right.

Let's move on to date specific tools looking at the ad column menu in our query editor.

We'll see those date and time tools grouped here on the right.

And these are actually pretty straight forward.

Got a few options here.

One of the more interesting ones right at the top of the list is the age option and then actually automatically

calculates the difference between the current time based on that today or now function and the actual

date in each row.

You also have the date only tool which basically looks at any fields that might contain both a date

and a timestamp.

And it strips the time component out and leaves you with just that date only.

You also have your pretty basic set of date component functions like year month quarter week and day

and that basically just extracts individual components or individual attributes from a date field.

And if you're selecting a field that has a timestamp as well you also have similar time specific options

like hour minute and second to other ones that I want to call out here earliest and latest you'll see

those two at the bottom of the list and they're both great out.

And that's because we're in the add column menu and both of those functions act just like the statistics

tool and they return a single value so to access those two options you'll need to grab them from the

transform option.

And as you might have guessed the earliest option is going to return the earliest date in the column

latest is going to return the last date.

So quick note here you'll almost always want to perform these date operations from the ad column menu

as opposed to transform since most of the time the goal is to actually build out new fields using these

date components as opposed to overwriting or transforming an individual date or time column.

So protip here you can actually do empower by quite easily is load up a table that just contains a single

set of dates or you can create a date table and then use these date tools to actually build out an entire

robust calendar.

So that's exactly what I'm going to show you how to do in a second.

But first let me give you a little peek of what that's going to look like.

So we're going to start with a single column containing dates.

We're going to apply those predefined date options using the add column tab in a query Ed. We're going

to transform that individual column into an entire calendar containing additional fields like the day

of the week the day name start of the week month quarter year et cetera.

So let's open up power I and give this a shot.

All right so if you're following along go ahead and open up your adventure works report file you should

see those two tables we've got customer and product going to go ahead and get data grab a third table

from a CSP.

And this time we want our adventure works calendar table so you double click Preview it's very straightforward.

Just a single column containing our dates.

Go ahead and click edit to launch the query editor and here we are.

So first two steps.

Table name first let's make it consistent a W calendar.

I could leave it here but since this is technically a look up table as well I want to be consistent.

So I'm going to add lookup at the end.

So a W underscore calendar underscore look up presenter obviously the data type looks good recognized

and formatted as a date.

And I've got a column header so check check.

We're good to go.

Now let's explore some of these date tools.

And I want to actually start with the earliest and latest options so I'm going to go to the transform

menu which again is the only place I can access these two options and when I click earliest it behaves

just like those stats functions transforms the entire table into a single aggregated value.

In this case it's one date January 1st 2015.

That tells me that's the earliest date in my sample so we can close that step.

Get back where we started and repeat the process for the latest date 630 30 2017.

So that tells us that the scope of the data set that we're dealing with is from January 1st 2015 through

the end of June 2017 or about two and a half years of daily data.

So I'll X out of that applied step as well.

Now let's shift gears into the add column tab and this time I want to create some new fields based on

this date.

So as you can see I've got all my options here.

Let's start with a day option and choose name of day.

So obviously that just created a brand new column exactly like we'd expect and it gave it a column header

by default day name.

Now I can customize those headers in this case I'm okay with that.

So I'll leave it as is.

And now it's important to note when you use these data tools that you have to have your date column

selected to access those tools.

I accidentally selected date name for instance which is really just a text string and I access those

tools you'll see that now they're great out.

You can't really do anything except pass that field since it's a text string so I'll select my date

again and I've got access to everything.

So another interesting one I want to show you is weak and I want to do the start of the week here and

there you have it as you can see power RBI has decided that a week starts on Sunday.

So January 4th 2015 was a Sunday.

The next week starts on January 11th which was the next Sunday and so on and so forth.

You can actually change this and it's not very intuitive.

You actually have to dig in to the M code which I told you we weren't going to do but I lied I want

to show you this just because it's a really helpful little tip.

And what you're going to do is expanded the formula bar here and you see where it says Date dots start

of week and then the word date in brackets I'm going to click my cursor right after the closing bracket

and press a comma.

Now there's two different ways to determine a custom start date for the week.

Number one I could type just a number which power be I will interpret as a day of the week and I know

for a fact that number one represents a Monday.

So if I just type that one and press Enter now you'll see these dates all shifted by one.

So now the start of the week is on a Monday the 5th the 12th the 19th etc. You can go back in and press

two you'll see a shift again to a Tuesday.

So that's the first way to update the second way which is a little bit more readable is to actually

type de dot and then the name of the weekday.

So day dot Monday and there you go I've got my Monday start weeks January 5th 12th etc..

So let's go ahead and leave it just like that as is our collapse my formula bar and I remember I have

started a week selected I'm going to go back and select date before access these tools and let's go

ahead and grab some month yields.

So I want the start of the month Jan 1 scroll down.

Fab one looks good.

Select date again let's go ahead do the month name at the bottom here.

Name of month.

There we go.

And then we'll do two more for the years.

I don't really need quarters in this case.

So let's do the start of the year and then the last one.

Let's do the actual year itself so year and that looks like a great start.

I think that's pretty much all that I'll need I've got date the day name the start of the week start

of the month name of the month start of the year and the year itself.

And so at this calendar table will allow me to do is filter and segment the values in my data set in

my data model by any of these fields that I've just created.

So it will allow me to look at things like sales not only by day but by week or by month or by day of

the week.

And that's going to be really helpful as we start using more of these analysis and visualization tools.

So our calendar is good to go.

Let's click on our hometown press clothes and apply and it will load it up into our model and there

you go we have our third table accessible here.

Let's check the relationships view three table objects with all of those fields which is created based

on that one single date column.

And there you have it.

Creating date tools.