How do you adapt the Power BI calendar
table to work for the movie industry?
Now this question was asked by
Hans Schopen from Munich, Germany,
who is lucky enough to have been working
in the movie industry for a long time.
And Hans is also a member of
our learned Power BI program and
that's where he asked his question.
And what he described was how the
movie industry tracks his data and
how the movie week starts on Thursday,
because that's when the first release
of the movie, new movie comes out,
the movie weekend.
Is considered to be Thursday
through Sunday and weekdays are
Monday through Wednesday.
Now movie week number starts on
the 1st Thursday of the year,
so the film year starts on the 1st Thursday.
And of course this all can
change for international markets.
But let's look at just this part
of the solution to see how that's
going to look like.
So let's just,
I'm going to show you a solution file.
So here I have the film calendar table
set up and it's got everything that
we talked about that Hans was looking for.
So it has the film here
and let me demonstrate.
So if you go to 2022,
I'm going to drill down.
And you can see the weeks,
weeks one through 52.
But these are film weeks, right?
So if you click on week one,
notice this is the first week
of the year 2022.
And just as Hans described
in the movie industry,
it starts on a Thursday, right?
So the week runs from
Thursday through Wednesday.
And of course if you go to week two,
it's Thursday,
January 13th and so on until the
last week of the year, right?
And you notice that the the the last
week and bleed off into the next year.
And of course we start all over again in
2023 with the first Thursday of that year,
right.
So the week numbers are all set
up and of course you can see,
you know, kind of the weekday.
So again by weekday when you're
looking at it, it starts on Thursday.
You can do kind of year to date.
And again, this is all by film year.
And the film week.
So, So you can see 2019.
This is our cumulative year to date.
Again by the film week,
which runs starts from Thursday
2020-2021, 2022 and 2023.
Some interesting data here.
The movie industry certainly trying
to come back after the COVID.
All right, great.
So let's come back to our problem and
go step by step and try to solve it.
Now, we're not going to be able
to solve everything in this video.
We're going to focus on solving this
part and then I'll let you know where
you can find the rest of the solution.
So for this,
let's jump into click on Transform
Data and that will open up our query editor.
That's what I call the kitchen of
Power BI and what I've done here.
Is I have some movies data here,
which is just the data that
I was showing here.
But I've taken the calendar table,
which is the ultimate calendar
table by the way,
and we're going to make sure that
we link to that in our resources,
right?
So you're going to find that either
in the corner or down in the
video notes where you can download
the ultimate calendar table.
Now the ultimate calendar table
isn't called ultimate just because
it has everything you need.
Well, that will be kind of crazy and it
will have then a lot of useless
stuff which you don't need,
but it's ultimately customizable.
And that's what we're going to do.
Actually, I'm going to start over.
So let me delete this guy and let's
start with our calendar table,
the ultimate calendar table.
So I've just copied it.
And again,
you're going to have the link and
the resource to access it and we're
going to reference that table.
So this one,
I'm going to call it film Calendar.
And what I'm going to do to begin
with is I'm going to get rid of some
of the weekday information that was
built into the calendar table because again,
we want to focus on the movie
industry idea of a weekday starting
from Thursday and so forth.
So I'm going to select the
weekday NUM weekend,
weekday week sequence NUM,
current week offset.
Yeah, I'll leave the weekday.
I mean, Sunday is a Sunday.
That doesn't change for movies, right?
So I'll.
I'll remove these columns and let's
start with the weekday number again.
So let's go to the Dates column
and I'm going to click on the Add
column over here and I want to say in
the Dates section I want to choose
the day day of week.
All right, now notice what it gives
you is that it marks Sunday as a 0.
Which isn't quite what we want, right?
We want Thursday to be kind
of the first day of the week.
And thankfully that is just an additional
measure you can pass in the default
function that Power Query added here.
So let's go there, Dateofweekdate.
And then we're going to say that give
me the day starting with Thursday.
All right, that looks good.
And if we check that again,
you can see here Thursday is
now the first day of the week.
Let's do the other column,
which is weekend and weekday.
So I'm going to go here,
actually add column, custom column.
And so our weekend is really Thursday,
Friday, Saturday, Sunday.
That's the logic that
Hans instructed us to use.
So 0123.
So I'm going to say if.
The column that we just added
day of week. I might even rename it later on.
If the day of the week is less than
equal to 3, then this is a weekend.
Else it's a weekday that
looks good and I'll just
prefix everything with film.
Film weekday weekend,
just so I can keep it clear.
And I'll also change the day of the
week to say something like Film Week.
Film Dana.
Film Week Dana, How about that?
All right, let's see how
this looks in our data.
Yay, we got our data loaded,
so our table is there.
And the first thing we're going
to do is link our data table,
the movies table,
to the new film calendar table.
So I'm just going to drag and
drop and connect these two.
All right Now it sets it up as
default because I actually don't
have very good movie table data.
It's not very granular,
but I'm going to set it up so that if
I have better data at a daily grain,
it's going to still work.
It actually needs to be a one to many
relationship from the lookup to that.
So I'm going to and by default.
It's also a good practice to have
A1 directional relationship and not
use a bidirectional relationship.
This is what I mean by default.
This is your lookup table,
this is your data table,
and this is exactly how the
relationship should look like.
One on this side, many on this side,
and single directional.
So that's set up.
Now let's go to our visualization.
I already created a measure,
so let me just put that on there.
So this is the data coming
from the movie table.
And let's see now if we can
start using some of the new,
sorry, the new columns that we had created.
So yeah, so this is. Let me see.
I just want to make sure
this is by weekday NUM.
Let's see if we can do the weekday itself.
And I just want to confirm that it is
indeed Thursday through Wednesday.
Remember, that's the way we are
trying to set up the film week.
Hold on, let me see.
What did I do with film Weekday?
Oh oh, I never renamed it.
Sorry guys, One more trip to the query
editor and the weekday is still a weekday,
Sunday, Monday, Tuesday. But.
Maybe this one is different because
we want to sort it differently.
We want to start the week at Thursday.
So I'm going to change it just
so I have clarity on that,
that this is a film weekday.
Let's bring that in again. All right.
So I'm going to drag that film weekday.
Let me see what's going on. All right.
I had to reload the calendar table again.
It didn't have all the years.
Now, of course,
I'm not a huge fan of the column chart.
It defaults, it sorts descending.
So we're going to change that and
we're going to tell it to sort by the
weekday and then sort it by ascending.
Now notice that this doesn't
look very impressive,
and if you would look carefully you
would notice that what it's doing is
it's really sorting alphabetically.
Which is not what we want.
Now normally when you're showing a weekday,
you would sort it by weekday NUM in a
regular calendar, the ultimate calendar.
There's already a column with that,
but again, it's sorts by Sunday 1st.
All you have to do is for the film weekday,
we're going to sort it differently
and say sort by the film.
Film weekday number,
which again starts at Thursday,
Thursday, zero.
Once we do that, notice here it,
it sorts correctly, right?
So the week starts on Thursday,
then goes to Friday, Saturday,
Sunday, Monday, Tuesday, Wednesday.
All right, cool.
Let's add one more thing, which is the the,
the, the weekday and the weekend.
Actually, I'm going to add it in this one.
And if you go.
To the top so we can start.
We can see kind of the revenue
coming through the weekday and
the weekend and I have both graphs
here so I can click and filter.
So notice how in weekday it
only filter to Monday, Tuesday,
Wednesday and if I click on weekend
it is Thursday, Friday, Saturday,
Sunday.
Now of course I can also instead
of that I can kind of drill down
here and look at the weekend of
course that's how you choose to do.
But you know,
you saw how pretty easily we can
customize the ultimate calendar table
to have a lot of different scenarios
like fiscal years where the year
starts in June or something like that.
And of course,
this is the first time I
had encountered film data,
movie data with the week starting like that.
Now. Hey, you can grab this file at the
URL shown here and we're going to put it
in the corner and in the notes as well.
Learnpowerbi.com download If you
have any questions of your own
that you would like me to answer,
just go to learnpowerbi.com Question if
you would like to see the full solution
step by step on how I built the film.
And the film week NUM and how I got
to show it on a cumulative basis for
the film year and the film weeks,
you can go to our advanced training
program at learnpowerbi.com Advanced Now,
on the other hand, if you're a beginner,
we also have our main Power BI course.
You should check that out.
I'll see you in the next video.
Until then, power on.