Original subtitles

I want to share protip with you guys that I think is pretty cool and it's creating a rolling calendar.

So in the case that we just walked through we loaded up a discrete set of dates because we only wanted

to analyze the data that we had on hand in our particular case.

We won't need to change that calendar.

We won't need to pull in new data or analyze new information over time.

But what if you're in a situation where you do you know maybe you've built a weekly report that you

want to refresh with new data every week and share with your colleagues or your boss or your client

rather than creating a fixed calendar and updating it over time.

What we're gonna do is actually use a little bit of code and I know this is the last bit EVENT THAT

WE'RE GONNA TOUCH.

But trust me it's worth it when you use that code to create a calendar that will update based on the

current point in time so it will act as a rolling calendar that evolves as time goes on.

So I'm going to lay out the steps here and then we're gonna jump in a power by and I'll demo what that

looks like.

So step one this is gonna be a great time to test that blank query option that I showed you earlier.

So create a new blank query and then enter formula bar.

We're gonna generate a starting date by entering something called a literal and you're gonna write it

exactly like it's shown here with a hash mark the word date and then a starting date whatever you choose

in the year month date format.

So in the example we're looking at here on the slide this would represent January 1st 2018.

From there you're going to click the effects icon which adds a new custom step and you're gonna enter

the following M formula.

Now I know it's a lot and I'll post this function into the Q and A section as text so you copy and paste

it but we're not really going to dig in and unpack how this formula is working we're just gonna use

it 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 and then we'll format that column as a date.

Last but not least if you want to you can add some more calculated date columns just like we showed

with our last example.

You can add years months week whatever you need and using those add column options.

But it's really as simple as that.

So let's hop in to power b I am going to write out this formula and I'll show you exactly how it works.

All right so I'm back in my adventure Works report this time I'm gonna go into the get data option but

instead of choosing CSB I'm going to pick that blank query option down the bottom.

This takes me straight into the query editor and creates a new query just called query 1 and if we wanted

to we could give this a name like rolling underscore a calendar.

Don't worry too much about this because I will be deleting this query after I write it but just for

the sake of habit you'd want to give that a proper table name and then inside of the formula bar.

This is where we're going to type that formula that I just reviewed.

So first step was to use the literal equals pound sign or hash date open parenthesis and then for the

sake of example let's use 2018 comma one comma one that will give us January 1st 2018 and when we press

enter it just basically creates a single value.

1 1 2018.

So that's not very helpful yet on its own but it's a good starting point.

The next step is to click this effects button to add a new step.

And right here where it says equals source this is where I'm going to type that long end formula that

I shared with you.

So follow along.

I'm going to move kind of quickly but go ahead and posit once I enter the whole function so that you

can make sure you've got it written down.

So we're gonna start with list dot dates open up parentheses source comma no dot from open print date

time dot local.

Now quick open and close parented just like the now or today function in Excel.

We're gonna close out one more parentheses minus no dot from open perennial source closed print comma

pound sign duration open print one comma zero comma zero comma zero and then two closed parentheses

to end it now without diving too deep into exactly what this m code is doing in a nutshell what we're

saying is calculate the current date compare that against the literal that we just created which is

our starting date and then based on the difference between those two dates I want you to list out all

of the individual values where the duration is one day.

So it's basically saying give me a list at daily granularity between those dates the literal and the

current date based on today.

So I press enter.

There we go.

It created this whole list and look at this it starts at 1 1 2018 and as I scrolled down through it

ends at 416 which is the current date it's April 16th 2018.

As I'm recording this and if we were to refresh this query a week from now that end date would extend

seven more days.

So we've essentially created this dynamic rolling list of dates.

That was the hard part.

Now all we need to do is click this new list tools tab that popped up and we're just going to click

this button on the left to table and it basically converts it into a table.

There's no delimiter.

We shouldn't have any errors here so we can just press okay.

There we go.

Final step is to format and name the column and then we should be done.

So go ahead and format as a date instead of column 1.

You'd call it something like date and now we've got a single column date table just like we started

with in the last demo.

And from here we could go ahead and add as many of those calendar fields as we wanted to.

So really great tip that I use a lot in my own work.

I manage a ton of reports that have to be updated very frequently.

And this rolling calendar is a huge time saver for working with reports like that.

So for the purposes of this course and this demo with our venture works data we actually don't need

the rolling calendar.

We're going to rely on our A W calendar lookup table instead.

So I know we just did all that work we could keep the calendar here if we wanted to.

Or in this case I'm just gonna right click and delete that query really just want to focus on the one

calendar table that we've got for the sake of this analysis.

But there you go.

Let's close out of the query Ed..

And we're back to square one.

So that's your pro tip how to create a rolling calendar.