Original subtitles

Instructor: I wanna share a pro tip with you guys

that I think is pretty cool

and it's creating a rolling calendar.

So in the case that we've just walked through,

we loaded up a discreet set of dates

because we only want to analyze the data

that we have on hand.

Now, in our particular case,

we don't need to change that calendar.

We won't need to pull in new data

or analyze new information over time, right?

Everything's fixed.

But what if you were in a situation

that you had to, what would you do?

Maybe you've built a weekly report that you wanna refresh

with data every day of the week

and share with your colleagues, or your boss,

or your client.

Rather than creating a fixed calendar

and updating it over time,

we're gonna use some M code to create a calendar

that will be based on the current point in time.

So it'll act as a rolling calendar

that evolves as time goes on.

So I'm gonna lay out all the steps here in the slides

and then we're gonna jump into Power BI

and I'll demo what it looks like.

So step one, this is gonna be a great time to test out

that blank query option that I showed you

a little bit earlier in this section.

So we'll create a new blank query,

and then in our formula bar,

we're gonna generate a starting date

by entering something that's called a literal.

And you're gonna write it exactly as it's shown here

with the hash mark, the word date,

and then the starting date, whatever you choose,

doesn't matter what the starting date is,

but you wanna make sure

that it's in this year, month, date format.

So in the example we're looking at here on the slide,

this would represent January 1st, 2020.

So from there, you're gonna click on the fx icon

which adds a new custom step,

and you're gonna enter the following M code.

Now, I know it looks like it's a lot,

and we're not really gonna dig in

and unpack how this formula is working.

We're just gonna use this almost like a recipe

to create our list of dates.

So once you've added that formula,

a list will populate in the query editor,

and then we're gonna convert that list into a table.

Once you've added that formula,

a list will populate within the query editor,

and we're gonna convert that list into a table,

and then we'll format that column as a date.

And then last but not least, if you want,

you can add some more calculated date columns

like we showed in our last example.

You can always add years, months, week,

whatever you really want or need

using those add column options.

But it's really as simple as that.

Let's go hop over into Power BI,

and we're gonna write out this formula,

and I'll show you exactly how it works.

All right, so we're back inside our AdventureWorks Report

and we want to come to New Source, get Blank Query,

and this opens up a brand new blank query for us.

So for the sake of consistency, let's update our table name,

and we'll call this Rolling Calendar.

We'll lock that in.

And then the next thing we wanna do

is come up to the formula bar here, right?

And our first step is to use the literal.

So this is going to =# or pound

and date and then open paren and 2023,1,1.

So I'm gonna start this at the beginning of 2023.

We'll hit Enter.

And when you press Enter,

basically this creates a single value for January 1st, 2023.

So again, not super helpful on its own yet,

but the next step is to click on the function button

and we're gonna add a new custom step.

And you can see here where it says source.

This is where I'm going to type in

that long M formula that I shared with you.

And I'm gonna type this out fairly quickly

but I'll kind of talk it out.

So feel free to pause the video as needed.

All right, so here we're going to start off with list.Dates

and I will expand the formula bar here.

All right, and the next piece that we want to add in here

is source, and then Number.From open paren,

DateTime.LocalNow.

And then we'll do open close paren

like the TODAY or NOW function in Excel.

And we'll do - Number.From(Source), right?

We'll come over, we'll add one more comma

and Shift + Enter to return down.

And our last piece here is we're gonna add

in a duration (1,0,0,0), right, end paren.

We've got our closing parentheses, right?

We'll click enter to lock this in, and awesome.

You can see that we now have a list of dates

that are generated here.

So kind of scrolling back up here

without diving into exactly what this M code is doing,

in a nutshell,

what we're asking it to do is to calculate the current date

and then compare that against the literal

that we just created, which is our starting date, right?

And then based off the difference between those two dates,

I want you to create a list of all of the individual values

where the duration is one day.

So it's basically saying,

give me a list at the daily granularity between those dates,

the literal and the current date based on today, right?

So when we press Enter,

we can see that we have created that whole list.

See that our start date is here on January 1st, 2023.

And if I scroll down to the bottom here, keep going,

I'm down to April 20th, 2023,

which is the date that I'm actually recording

this lecture right now.

So the cool thing here is that if you come back tomorrow

or next week, or in a month, and you refresh this query,

all of those new dates will be added

or appended to the end of this list.

So that's basically what's going on here with this query.

Pretty easy to write out.

One thing to keep in mind here

is that M is case sensitive, right?

So if you did ld here for List.Dates,

that would throw an error.

So if you're running into those types of errors as well

make sure that your syntax

along with your capitalization is also correct, right?

So the next thing that we need to do

is we have this List Tools,

contextual menu that's popped up,

and we want to click Convert to a Table.

So here from this window, convert to table, right?

There's no delimiter, and we shouldn't have any errors

so we'll press OK.

We can see that we convert to a table.

From here, we want to do our one final step

is update our column name.

Let's call this date.

And then we also want to update the data type, right?

And we can set that to a date.

So that looks great.

And what's cool here is this is ready to use

just like the other date tables that we've imported

as we can click here, add column, go back to date,

and we can start adding in calculated fields like year.

Go back, we could add start a quarter

and maybe the last thing we want to do

is add start a month, right?

So in a few minutes with a little custom M code,

we're well on our way to creating

a scalable rolling calendar

that is gonna update based on the current date.

Now, one thing to keep in mind here

is that for the purposes of this course,

we don't actually need this rolling calendar.

We're really gonna rely on the calendar lookup table

that we've created instead.

So feel free to keep this query or delete it.

All right?

So that's gonna wrap up your pro tip lecture

on creating Rolling calendars.