The date changes the information shown here changes in this video i'm going to show you step by step how you can create this now if you are a power bi beginner or.
Still feel like a beginner then my friends this channel is just for you so make sure to subscribe and click that bell so you are notified whenever i answer your power bi questions now this.
Question was asked by norman norman one of our learn power bi members and norman actually sent this in and you can see that's exactly what he was asking for he said oh i want to show the last week.
Dollars i want to show the current week dollars and i want to show the delta the difference week over week but the most important part that it is always last week encountered week so that means it.
Uh to get your questions answered all right so this is what we're trying to build now where are we going to start our solution is is this point where we have the.
Concept of the weeks and the current week offset essentially built into the model where do we get this information i'm going to share that with you in just a.
Moment but i want to show you how our model is set up so of course we have our sales table that's what we focus on and our calendar table those are the only two that matter for this one and.
You can see in our calendar table we have the week start date and in this case we chose sunday as a start date but we also have the current week offset and that's the magical part which is really.
Going to help us in the next step when we start building the solution that we need so let's go back here and you can see current week offset it's easiest for me to explain this.
Posts Related:
If i scroll down to the current week now
You can see here so today's date is august 4th and you can see that august 4th kind of lies between this and this so it's marked this week as the.Current week so probably you can see how it's working that the zero always marks the current week and again it's fully automated it's going to update automatically as you go into the next.
Week and the next week you know the xero will always keep marking the current week right now it is the week of july 31 and you can see that uh it what it also does is marks the.
Previous week as minus one minus two minus three and if you have future weeks uh one two three there are marked as well so this this is gonna make our work so easy let me show you one more thing.
Here just to just to emphasize and make it clear what's happening so if you click on this current week you can see that it spans the full week it has the dates from starting from sunday monday.
Sunday july 31 to saturday august 6th and if you were to go to the prior week 7 24 again it starts at sunday july 24 and goes to saturday july 30. so that's how it's set up now of course i had said.
I'm going to tell you exactly where you can get this all set up you don't have to do anything and that is of course part of our ultimate calendar table and we're going to link to the ultimate.
Calendar playlist go watch that and you're going to know how that table works and how you can use that table and download that table so that is our.
Starting point now with that in place let's get started on our solution all right so now of course we have a simple measure defined which is sales let's add this now of course what this is doing is.
It's showing you
The sales for all sunday monday tuesday wednesday thursday friday is not yet showing the current.Week because of course we haven't taught it how to do that this is a simple measure sales sum of sales amount now of course we could manually filter it right so i could go here and find the current.
Week which is august july 31 actually is the start of the current week and i could click on that and we'll see the value shift but of course we don't want to do that manually.
We definitely want to do it automatically so how do we do that so for that we're going to remove the sales measure from here and we're going to build a new measure so let's go here.
New measure all right and let's say sales current week equals we're going to bring out our trusty calculate so calculate what are.
We calculating well we're still calculating sales but we're going to alter the filter context a little bit i'll talk about filter context in a second so calculate.
Sales but only for the current week and that's where our built-in ultimate calendar table is going to be super helpful so we can go to calendar and we can say cur.
Week offset you've already seen that in action equals 0. we know that that always gives us the current week as i showed earlier in the table great so let's say yes to that i'm going to.
Format it and then place it on our table and let's see what it looks like bingo right so notice that i don't have to go click on filter find the current week all of that.
It's showing me current week and not just that it'll always automatically update to the current week now of course if you're here you can easily guess how we're going to write the rest of the.
Measures so i'm just going to copy this one and create a new one and we will create the sales prior week so we go in here prior week and in this case offset i'm just going to change it.
To -1 that always points to the previous week i'm going to format this again just change the currency there and sales prior week.
And there we go so it's showing current week prior week and of course you've heard me probably talk about the lego block approach of creating measures so we can take this and.
Do the change really easily and sales week over week change equals and again lego blocks once.
You've defined the measure you don't have to redefine it again you can just reuse that as lego blocks sales current week minus prior week.
And i'm going to go here change the formatting bring this in and we can add some formatting so let me go here.
And i'm going to say conditional formatting let's do data bars probably the defaults are okay and there we go we have some color so that's how you can create a report which.
Always shows you the current week and also lets you compare it with the prior week and everything updates automatically now we talked about a few concepts here.
Which may be new to you maybe calculate i mentioned filter context a little bit which is a really important concept to understand if you want to learn more about a complete power bi training.
Program then go to this url we're gonna put a link in the corner and in the description below i'll see you next time until then power on.