Today we are talking about week numbers in Power BI calendar table. Now calendar table is clearly
one of the most
important tables in Power BI
almost a table
Which every model should have and having a week number in there
allows you to report your metrics and numbers your sales in this case
week by week as you see that here
and as you see this in this graph on the green line. That's my weekly sale so you can see this is Sunday
May 22nd and Sunday May 29th, of course, you can choose a different start off your week.
I'm choosing a Sunday here, but it can also allow you to do things like a rolling average over the past ten weeks
So this is kind of week by week and it fluctuates where the rolling averages smoothes that number out
So let's get started. My name is Avi
I'm a Microsoft MVP and a best-selling Power BI author
and if you would like to become a Power BI Pro then make sure to subscribe and ring that bell my friend that's important,
so you are notified whenever new videos go live and I go live
every Friday to answer your Power BI questions.
As I said calendar table is one of the most important tables in power bi and I have discussed this extensively on my channel
So if you head over to my channel and we can actually link it in this in this video as well
But if you are on my channel
Then look under the playlist section and that's where you're gonna find the good stuff including the ultimate calendar table right there
And that has all the details about how to build a calendar table
You can download the table for yourself and just use that by copy pasting it in your own model
now what we're focusing on today is
Is the week number and our task today is something that was sent by
Marsha son, okay
I probably butchered her French name but Marcia asks that avi
Your calendar table does have the week sequence number already been built in and it's pretty awesome. It lets me do things like this
ruling averages as I showed you it also lets you do even more advanced stuff where let's say you only want to show
Numbers for the last five weeks and have it automatically update as time goes on you can do all of that already
But what she's looking for is is there a way that I can make the week sequence numb?
Restart each ear. So that's the task. That's the quest W on for today. So again, we have this V
So what she's talking about is is the way the week sequence number is set up is it starts with the first week at?
The beginning of your calendar table and my calendar table starts in 2014, January 2014 and and goes up from there
so the next Sunday we get two and so forth, but if you go to
2015 then it starts at 53 and if you go to 2016 it starts 105 so she wants a version of this which
Restarts every year now again
There is a reason why this one is sequential
Increasing because only then can you easily do things like rolling average? You can do like past 5 weeks and so on
But again, let's see if we can help Mara with her task of restarting week sequence number each year now
I will give you kind of three answers to this
The first answer is if you're really truly interested in week numbers
And if you want to do it the right way, then my friend you owe it to yourself to explore
the four or five fold retail or
Merchandising calendar just google that and you'll find something like this and what this does is
it makes sure that week boundaries are consistent so you can see that every year every
Week is always a full week
There are no partial weeks in this calendar and that means if you're always comparing apples to apples
So again, if you're really truly serious about you know
Kind of weekly and making sure you're comparing apples to apples then. This is the way to go
But assuming that you're not into that stuff
Then using a regular calendar table then. Let's see how we can make this work in weeks English num. Now my first idea
Was this so I'm going to show you two approaches in in this regular calendar table and you can pick which one you like more
The first approach is that I'm gonna go to my calendar table. I would have my date and
Essentially ignore the existing week sequence number that I have in my model
So I'll ignore that is already being built in and again
You can see this is the ultimate calendar table again
Watch the rest of the videos that I pointed to to get more details on that
but for now
We'll just go to this date column and I'm gonna say I want to add a column
Which is based on this date and I would like to get the day of year, right?
so so again, I just kind of went in there and said day day of year and I'm expecting something like
One to 365 so we'll say day of fear
And if I look over to the side perfect
So day of fear kind of one and if I scroll down I expect it goes to 365 and then resets which it does
Right so you can see 365 and resets. So that's exactly what I want. And then I'm gonna add another
column
Mm-hmm. So we're gonna say add add column based on that and we'll say divide
Divide
By
7
All right, so you can see I'm trying to do the weeks here
Now, of course, this gives me a decimal and what I need to do is just round this number up
Round it up. Let's go find that so this time I don't want to add a column
So I'm gonna switch to transform I want to operate on this column itself and transform looks similar
So I'm gonna look under the number thing and let's see rounding that seems processing
Promising not processing as you can see round up is right there. So I'm gonna select round up and
now, let's see so we have
A series of one series of two's looks good. Let's go back into our table. Let's give it a good name
First of all not division. Let's say week
week number
Week num and I'll say based on
Day of here now, of course if this was a real model, I wouldn't call it that I'm just giving it a descriptive name
So we can keep things straight in this video. All right, so let's go back here and
I'm gonna go grab that new column that we just created week number based on year and I'm gonna switch to
2014 so
2014 it starts off at 1:00
But remember our goal was that it should
Reset every year and I'm gonna turn the totals off because totals don't really make sense in this table right now
Right, so it starts off at one goes up at two and so forth. Let's see if it does reset 2015. Boom
It starts off with one again, 2016 and again
You can see that kind of goes off one from 1 to 52 or 53 in case there was a partial week there
however
Once I used this approach, I wasn't a huge fan because the way that this is working is in
2014 the week 1 is just the first 7 days so it goes from
Wednesday at a Tuesday and if you look at
2015 it just goes from
Thursday to Wednesday week 1 so it's somewhat arbitrary and I didn't like this approach
So what I wanted to do
So I'm going to show you the second approach where I'm gonna go back to my original
Week sequence num use that because it's it's Sunday based
So I like that and of course you can change that if you want your week to start on a Monday, that's easy to change
In the calendar, but I wanted to stick with that Sunday definition
but all I want is is it not to keep going I wanted to reset so let's think about this and I always say that
Human learning comes before machine learning. So how the human do it. Well, if I were to do it, I'll just figure out like in
2015 if I'm looking at all of these states, right?
All I have to do is figure out the smallest number. The smallest number here is 53 and just subtract
52 from everything so that means that
This becomes 1 and if I subtract 52 this becomes 2 and this becomes 3 and so forth. That's it
So that's human learning and now let's teach power bi the exact same steps
So we're gonna go back to our query and a calendar table
and I'm gonna add a
marker
Which I forgot to do initially what I'm just gonna insert a dummy step and and just call it saying that hey we're working on
something here, so we'll
Grab this thing and let's let's call it
new
Week number something like that, right?
So again, I like leaving those markers especially in complex query and you can see this one has quite a few steps
So now let's add another
one of the steps here and
Rename again, you know, let me make this window smaller. So that's not off in a corner over there
So here I'm gonna rename it and I'll say week num
approach to
Okay, cool, so we can approach to we're good there. So remember our
Goal was to figure out the minimum week sequence number for each year and then use that to subtract it
right
so
So for that
what I'm gonna do is I'm gonna go to my query and I'm gonna use a group by so I'm gonna say group by
group by here
so for every year
I want to figure out the minimum off the week sequence number that we already have and I'll say
max minimum week
sequence number
Like a week sequence on week number, right?
So, you know, okay, and and there we go, and I have that exact number now
Remember, I you know, so if you go back to 2015, I would like to subtract not 53 but 52 from each one
so I'm gonna
Take this and then transform this and I'm just gonna subtract one from this to get the actual number that we want to use
So perfect. So now we get 52
So that's the number that if we subtracted 52 from each of these numbers, then we would get the number that we are expecting
So now let's use this and merge it back to her original calendar table
So what we're trying to do is this is a result right now in this row
And we're trying to merge it back to our original calendar table, which is sitting over here now
I'll show you the way I do it. So what I do is I go in here and
Transom and I'll say merge queries and I'll merge it to itself
so I'll say Calendar current table itself and I'll merge it on here and hit OK and
It generates this M syntax for me a kind of lazy about M
Yeah, I rather generate for me
And so it did the merge but the thing is that I wanted to merge this step
Subtracted from column from week num approach and you can see the syntax
All I have to do is change that up here
So I'm gonna grab the name of this step and go back to my merge and say no no
Don't don't her subtracted column this step with this step itself merge it with
The previous step and here. It's its name. That's it simple as that and now our merge is done
so what we did was we ended up merging our
original calendar with the the minimum week sequence number
So now let's go back and continue over here
So now I have my calendar table, but on the side. I also have the minimum week sequence number
I'm gonna grab that value and I'm gonna create a new column and
let's call it week num and this is
based on
we
sequence num
Being reset follow let's just call it that so week num new week
No based on this and we're gonna take our original week sequence num
And just subtract this this number that we just calculated in the query editor itself
That's it and we can throw away this column this is a this is not needed anymore so we'll remove this one
that was just like a working column and I love doing this part in the query editor and
Let's see how it looks in action
So this was an original week sequence now, let's go back to
2014 and we're gonna bring in the new one, which is supposed to reset now, of course right now. We're looking at 2014
So that's where my calendar starts but this is a real test if he switched
2015
does it continue on or does it reset let's switch to 2015 and boom you can see that it resets because again
Hey, we built this whole thing, right?
We calculated the 52 number and all it's going is it's subtracting the 52 now, of course, if not just works for this year
But it works for every other year
And again, it's using that original week sequence number that we had when out resets it tomorrow
Hopefully that helped you my friends. Hopefully you like that approach
Hopefully you learn something new again calendar table is one of the most important tables that can be in your model
So go ahead in my channel and grab the ultimate calendar download watch all the videos
Let me know what you think in the comments down below make sure to subscribe and hit that like button
Until next time bar on my friend
Hey keep watching more videos and deep learning power bi but if you did enjoy this video
I would love to hear from you. So leave a comment like subscribe
All the good stuff while we're on my friends