All language subtitles for 12. DAX Demo Basic Date & Time Functions

All right.

Time to kick off our Dax demos.

Now we're going to start by focusing on calculated columns first since that a bit more user friendly

a bit more intuitive.

And we're gonna kick it off with basic date and time functions.

So here's what we're gonna be dealing with.

Now all you sell users this should look really really familiar.

These basic date time functions are very user friendly really simple syntax.

So this should be a breeze starting with day month year.

I call these component functions because they essentially point to a date and extract some specific

component of that date day the month or the year.

So syntax wise incredibly straightforward.

You just point it to a column containing your dates and you're good to go.

Same thing holds with our minutes second.

The only real difference is that you have to point to a column with a date time component as well because

what these hour minute second functions do is they ignore the date and only focus on the hour the minute

or the second of the timestamp component.

Now today and now those are volatile functions which means that they update or refresh every time there's

a workbook change and those two functions return the current date or the current exact time respectively.

Syntax wise literally could not be simpler.

This function doesn't even take a single argument you literally open and close the parentheses and you're

all set.

Now week day and week number two of my favorite date functions here weekday returns a weekday number

by default from 1 to 7 representing Sunday to Saturday and week num captures the week number of the

year.

So for syntax first argument is the date column.

Second argument is the return type and this is where you can tell power by exactly how you want your

weeks to start whether it's a Saturday or Sunday or Monday etc..

Now EO month another really helpful one believe it stands for end of month because it returns the last

day of the month plus or minus a specified number of months.

So here's what the syntax for EO month looks like.

You point to a start date and then you specify that number of months.

So if you use a zero for the second argument here the month function will essentially give you the last

date of that current month.

If you use a negative one it would give you the last date of the previous month and vice versa.

Now little protip here even though it's a bit of a misnomer you can use the EO month function to return

the beginning or the first of the month as well.

I don't believe power by or Excel have any first of month equivalent functions but what you can do is

set that month's argument to negative 1 and then simply add one day.

So plus 1 at the very end of the function that basically calculates the last day of the previous month

and adds one more day to give you the first date of the current month and then last but not least we've

got date diff returns.

The difference between two dates but not only that but it allows you to select a specific interval as

well so date 1 your start date date to your end date and your interval which gets as granular as seconds

or as broad as years.

So a really helpful function for calculating the difference between two dates.

With that let's open up our venture works file and start adding some new date and time columns to our

model.

All right.

So once you've opened up your adventure works Report Let's navigate to the Data tab.

This is gonna be kind of our home base for the next few videos as we create these calculated columns

and measures to our model.

And I'll make sure you're following along because we're gonna be creating new fields new measures that

are going to be important for us to analyze and visualize.

Once we get to the next section of the course we start building reports and dashboards.

So pay close attention.

Try to follow along step by step.

Now the first demo that I'm going to use is the weekday function so I can right click anywhere inside

this table really doesn't matter where.

Select new column and I'll just tack that column right to the end of the table.

So let's go ahead and name this day of week and we're gonna use the weekday function here.

So as we start typing you'll see that not only does the formula or function name populate but a description

populates as well.

So really helpful tool tips and intelligence functionality that power RBI offers here.

So I could tab to lock that in and it takes me right to the first argument which is the date.

So let's go ahead and start typing to find that field type date which gives us the option there or we

type calendar to navigate to that table.

Either way it helps us find it.

And then again I can tab or double click to select that field.

And now with date fields specifically this additional list of options appears dot date dot day dot month

and so on all the way to dot year.

And this is power by being pretty slick.

It's saying OK I see that you selected a date field it knows that calendar look up date is formatted

as a date and it's giving us the option to either use the actual date itself by default or to use some

aggregated component of the date like the month or the quarter or the year just like those day month

year functions actually work.

But it's kind of embedded in here which is pretty clever.

Now I don't have to select any of these options or if I select dot date it would be redundant but basically

the same thing in this case I want to reference the actual date itself.

So I'm just gonna press comma and it takes me right to the next argument of the function.

Now remember this is the optional component the return type.

You can see that it's optional because it's shown in brackets in that tooltip.

And my options are one two or three where one is a week starting Sunday.

Two is a week starting Monday and three is another week starting Monday but from zero to six instead

of 1 through 7.

So I kind of like defining my weeks starting on Mondays.

So let's enter the two here closed the function off with the closing parentheses and then press enter

to lock that function in and there you go.

See we get ones through sevens where ones are my Monday rows sevens are my Sunday rows.

So there you have it pretty straightforward demo of the week day function.

Next up let's actually collapse the calendar and navigate to our customer table scroll all the way to

the right here and you'll remember we have this Berthier column which we had actually calculated within

the query editor.

So we don't see any kind of Dax formula here even though that was technically a calculated column.

So what I'd like to show you is how we can create that same Berthier column using Dax and then remove

the original.

So again right click anywhere in the table new column I'm going gonna name this one Berthier underscore

C.C. for calculated column just so that we can compare the two side by side and differentiate them.

So Berthier C.C. equals the year of the customer lookup birth date column.

So a tab to lock it in close the parentheses and press Enter now when I scroll over there it is Bertha

or C.C. against the original Berthier.

It all lines up which is good.

And you may have been wondering can't we use that shortcut power by tip that automatically extracts

the year without even writing a function.

Well yes you're very sharp.

You sure can.

Let's go ahead and edit this I'll show you how to do this exact same calculation using a different type

of approach so instead of using the year function explicitly.

What if we just point to the birth date field and then use power b eyes tip and say Just give us the

birthday that year or just the year component of that date field.

Presenter scroll on over and what do you know.

Give us the exact same correct values.

It's technically a third way to accomplish the same thing.

Calculating the birth year using the query editor or using both of those Dax or function approaches.

Now want to show you one other interesting thing which is obviously we don't need to birth year here

in this table.

So watch what happens when I right click and delete the birth year column.

So remember that birth year column that I just deleted.

That column was added it was calculated in the query editor.

So there was an applied step that added that calculated column to the table.

So now what you might be wondering is every time we refresh this customer table isn't that Berthier

column just going to keep appearing and appearing and appearing and the answer luckily is no.

Because power VII is very smart and we go to edit queries and navigate to that customer table and check

it out.

It removed the birth year column as a new applied step so that it's not going to keep recreating that

column.

It knows that we just deleted it from the table itself in the Data tab and so it saved us the trouble

of having to delete it over and over and over now.

The last date time demo that I want to show you formula to calculate the current age of our customers

so let's stick right here in our customer table and for this one Let's practice the date dif function.

So it's right click new column we're gonna name this one current age equals or start with the date.

Def to arrow down and tab that one in and I remember three arguments date 1 start date.

DATE TO THE END DATE AND interval.

So our starting date is the customer's birthday so there it is.

Customer lookup.

Birth date.

I don't need any of these time components here.

And then the date to the end date is going to be the current date.

So we use the today function Open Close parentheses for that one and then comma to my last component.

The interval.

This says OK.

By default I'm going to return the number of days between the two date arguments the start date and

the end date.

In this case we're looking for the current age in years so we can just scroll right down to year press

tab to enter it close the parentheses and press enter and we scroll on over there you have got current

ages seventy sixty nine sixty seven and so on and so forth.

So great way to use the date.

Def function in that example and now just for fun I'm curious if we change that last interval component

I wonder how many seconds old these people are close it off changed year to second

and there you go.

Looks like our first customer here Blake Flores is just under two point two billion seconds old which

is not very helpful for the purposes of our analysis and I don't think our adventure works clients are

going to care much about that but kind of interesting to see.

So let's go ahead and change that back to here.

Close it off press enter and now we have a more appropriate ages there.

So there you have it just a little sampling of some of those basic date and time functions.

We practiced week day today and date diff.

Next up we're going to talk about logical operators and some conditional if and and or statements.

Stay tuned.