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.
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..
- Design A Responsive Homepage With Full Screen Video Background - Part 4 BlogSpot
- 6 Professional Book Cover Mockup With Backgrounds Download In PSD Files English Photoshop Tutorial Office
- Inbound Calls Interactive DASHBOARD in EXCEL with Pivot Tables & Pivot Charts No Macros
You can do kind of year to date. - ID Card Make
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.
