We have the sales year to date measure i just showed you the formula it's a pretty straightforward formula and i love that simple stuff in power bi where you can do a lot of things very easily.
So sales year date you've seen the measure and sales prior year you've seen the measure but again as you can see right now we're in the middle of the year so it's may 10th.
And the current year has data only till may 10th right and but the prior year has data for the full year so it ends up being an unfair comparison let's look at it with the.
Help of excel i just wanted to show you in a visual so again current year you can see that it's only going to about you know kind of the may 10th date but the priory is a full.
That are we doing better or worse than last year so of course what you would want to do is you would want to compare apples to apples so you want current year year-to-date which is pretty.
Straightforward but how do you get prior year year to day that's what we're going to cover in this video now this question was asked by mike m one of our youtube followers and he posted a.
Comment on one of our existing videos which you should also check out i'm going to link to it at the end of this video power bi your date across multiple years interesting video but mike said.
Hey what about if i want to see a specific date like today's date how do i do that and that's we're going to answer today and of course if you have any power bi question you need.
Help answering then just go to learnpowerbi.comquestion so let's get started with this demo by the way you can download the file shown in this or any other demo here on learnpowerbi.com.
Posts Related:
Download all right my friends so
Hopefully by this time you kind of understand the scenario understand what we're trying to do so i'm going to show you.Two different ways to do it right and then you can of course see which one works best for you now what we're using in a model uh so this is a simple model that we have kind of the sales and the.
Calendar that's the only thing we worry about but for the calendar we are of course using our ultimate calendar table now if you don't know what that is about definitely check that out inner.
Resources the ultimate calendar youtube video you can get that free download and have this amazing calendar for yourself now we have a lot of bells and whistles built into this calendar and by the way.
The right place to build your calendar table is certainly the query editor and that's what the self this pattern uses it builds the whole thing inside the query editor and again that video we're.
Going to link to has all of the details for you how to download how to use it but the key thing that i want to point out to you are a few of them um.
One is this indicator which marks whether a future date or past date we are going to be using that right so again right now it's may 10th so [Music].
Everything beyond that is going to be marked as future everything may 10th and before it's going to be marked as and there's some other bells and whistles here like the current year.
Offset and for that zero marks the current year and again the videos on the ultimate calendar talk about in more detail all the other stuff in inside the.
Ultimate calendar table but what we're
Going to do is we're going to use that for our first solution in fact i'm already using one of those features right here so notice i'm using current.Year offset is 0. now how that helps me is that i don't have to reset it every year in january i used to do that when i first started out in power bi but you know.
That's yeah a sign of a great power bi developer is that you're lazy i don't want to do that every year right so it's just automatic every year it's always going to show you the current.
Year so it's awesome right so current your offset is zero so it is showing year 2022 and we can see that in the dates here as well but again as you can see here it goes till the end of the.
Year it goes to december and of course well we don't have data for the current year because well we haven't invented time travel right but we have data for the prior year and that results in the.
Apples to oranges comparison so an easy fix that we can do is that we can in the filters here we can go to our calendar table and bring in that future date field and.
Apply that as a filter and i can say you know what i do not want to see future dates so i'm going to click on that right so again i brought that filter in and i said just show me the past dates.
And that's it and you can already see that how the graph is very different so again let me take it off so again focus on may so may it's making it look like uh oops let's try this.
Uh so may you can see that it's showing the prior year there were 335 000 and this year it's like ah not as good 255 but that's not quite true because again it's apples to oranges when we focus on.
Just kind of the the current date till may 10th well then you can see that we are actually doing much better than last year last year till this date was 98 000 and well this year we had 255 000 so.
Incredible right and again you can see that in the table down here uh we're only going to may 10. so this is a quick and easy fix but this may not work every single time so let's talk about.
The second scenario so for the second scenario i wanted to show you something which is part of our advanced module but there are going to be scenarios where you can't filter down the whole page to.
Only show current dates because you want to show future dates you may not have obviously actual sales for future dates again unless you have the time machine but you might have forecasts as you can.
See this is the advanced lesson where we show kind of a kind of an actual lines and then we show a forecast line right and of course there's a budget line here as well but again so you're going to be.
Half scenarios where you cannot just simply filter down to the current date so um for that that means we we can't just really nearly apply that feature future date filter on the whole page now.
You can probably try to add that filter selectively on some some visuals but that's not a very clean solution and i i'm always wary of that because i kind.
Of forget it that i had the filter on it and so forth and it leads to trouble i would rather use the ultimate weapon that we all have which is dax so let's see how we can solve it in dax and i'm.
Going to take you step by step through that all right so i've created a kind of a working measure here and we're going to use that to take you step by step through the process right so the the.
Overall high level what we're going to do is is that of course first you have to understand without what's the incoming filter context right so in this case the.
Incoming filter context is being determined by uh this filter currently your offset is zero which effectively right now it means it is 2022. now of course the.
Magic of the ultimate calendar is the next year is automatically going to switch to 2023 but again so effectively you have dates from january 1 through the 7 31. so.
First thing we need to do is we need to kind of filter out the future dates right so we only want the ear to date so let's do that first so we're going to go here and again pull out our magic.
Wand which is calculate which is i always use to alter the filter context so you have the calendar and here we're going to use that future date filter and again earlier we had kind of manually.
Applied it in the filters pane but again you can kind of programmatically apply it using dax so it kind of gets built into this measure and this measure will always behave in this way so we can say.
That here future date i only want past dates i don't want any future future dates right so again so this is kind of the next step so incoming filtered context is.
Is uh you know it's full year and this is filters out past dates uh so again we're getting here now of course the answer.
Didn't change because we didn't have any future sales understandably so the answer is still the same but that's good this step is important and the next step is that once we have the filter context.
Which is looking at the current year year today then we can say and say hey go same period last year again kind of we know how to do the sales year-to-date we know.
How to do prior year but in this one we need to do both so i'm going to show you the wrong way to do it and the right way so the wrong way is what i tried first.