How can you compare the performance of the current year with that of prior years
but still have it be a fair "Apples to Apples" comparison
even though your current year is a partial year.
Of course, you can do that if you only focus on the completed months.
So let's say we were in the middle of 2018, we just finished October. The way to do a fair comparison,
I mean right now, you can see that these numbers are comparing a
partial year with the full prior year which isn't quite "Apples to Apples".
What we would like to do really, is to just compare the completed months or the year-to-date.
Now, this is the end result we're going for and we're gonna show you not one but two ways to get that. Keep watching.
Hey, my name is avi Singh. I'm a Microsoft MVP and a best-selling power bi author
And if you would like to become a power bi Pro, make sure to subscribe and click that bell
So you were notified whenever I go live to answer your power bi questions
So this question was asked by Sean McDonald from the sunny state of, California
And he was joining us in one of her live talk power bi shows
So again, you've seen the question we have the data in this case, and we're pretending that right now. We're in September 2018
I just wanted to use that so we can kind of show you the scenario a little more accurately. So
so here again, we have partial data for 2018 and we have full data for
2017 and
2016 and the end result we want is to just compare the completed months
Now, of course, we don't want any you know
we want all of this to run automatically if
September is the completed month right now the next month automatically it should pull in the the new month, right?
So as soon as we have data over here, it should automatically pull in the current year today
So that's the goal that we're working towards now one note about the calendar
We are using the ultimate calendar which you can go and find on my channel
Just go on the channel home page and look under the learn power bi playlist
But we have changed one parameter to again pretend that we're back in the future and we did that
To show you kind of the partial year as promised
We're gonna show you not one, but two solutions do this and we're gonna build it step-by-step at any point of time
You can click the link shown on this video to download file at the link shown here. So let's get started now first things first
When you think kind of year-to-date that language was used when Sean McDonald kind of asked that question
And and you might you might think back to the classic year-to-date
Formula which looks like this. Let's take a look at this formula really quick and it's a classic pattern. It's coming up right now
So, you know calculate sales dates here today uses one of the inbuilt time Intelligence functions
But that is not for this purpose. That one is for giving you cumulative totals across here
So you can see how dates here date kind of adds this up
Cumulatively over years and then resets at the beginning of the year and then resets again
And of course September 2018 in our scenario is a partial year
That's why getting a flatline at the end and there are other methods to solve that but again
That's not what we here to solve. We are here to give
Shawn this result. We're automatically it knows kind of what the completed month is and it's gonna say, yep, you know
We just completed August so I'm gonna show you data as of August and I'm gonna compare apples to apples
Now first of all, this there was a decision point here
and by the way
power bi data modeling often comes down to your the assumptions you're going in with and the decisions you make so the first decision point
That I had was do how to decide which is the current month now
I was tempted to go to my actual data set to say hey
I have data to August and use that but I I decided not to do that for two very good reasons
For one in a lot of cases you can have data for future days. In fact at Microsoft
we would have data for future months because there would be some revenue which would be recognized booked for future months and again,
They can be lots of other scenarios
We have data for future dates, but they're not what you're trying to show right now
And the other reason is that it may not be just one data set you might have multiple data sets and different
Varying levels of data maybe for one data set we have data to last week for another one
We have data last month and it all gets really confusing. So I think the right way to do this is to leverage your
Calendar so let's go back over here
We have is our calendar is the ultimate calendar again, you can find it on my channel
Just look for the ultimate calendar playlist and download that
so here
In one of the features for the ultimate calendar is the current month offset now idea is really really simple
So let's take a look at that. So what it does is and again we are pretending here
So we have set up a parameter in here to pretend that the current date is
August 30th, 2018 and
Let's go back to or current offset
Yeah, so what it's gonna do is it's gonna say that yep that month is the current month
So it's gonna mark it as zero and anything after that it is gonna mark it as one two, three four
And of course going back - one - two - three now
This is an extremely powerful concept lets you do really mind-bogglingly amazing things
And again watch the ultimate calendar playlist on my channel for that
Here, you know I just figured I was showing you my channel
So all the good stuff is right at the top in this learn power bi playlist. There is a tutorial there is ultimate calendar
There's modeling. Dax and so forth, right?
So that's where you can find that I so we're gonna use the current month offset to tell us that what the current complete in
Month is right. So great. So let's gonna let's build it kind of step by step
So again what we have right now here is just a simple sales measure, right?
So sum of sales amount and clearly it's not it's not working, right?
It's giving us these these year and totals which is exactly kind of apples and oranges, right?
So we're these are oranges which are being plugged in over here. And this is our Apple
Which is a partial year now 2018 was a ballistic year for adventureworks. They're doing great. You should definitely buy their stock
I would recommend that
But no, but yeah I said this is a complete year versus partial year. So that's what we want to change now
What I like to do is to solve the axe issues is to solve it thinking about
Like how a human would solve it and this is the tricky part because humans are intuitive
So you have to break down that thinking step by minut step
And then all you have to do is just teach those steps to power bi it's easy, right?
So how would a human calculate it? Well for one?
How would a human know that which is the current month and again?
So we decided that so again a human has to know that oh, it's still oddest
I have to stop at August and so we have to determine that well, how do we do that?
Well, we do that using as we discuss the current month offset. So let's
Let's go kind of solve that first
So I'm gonna write my very first Dax measure and
This is actually one that we're not gonna be using but I just want to kind of start there as a baby step
so let's go to the modeling tab over here and hold on one second all
Right. It was just acting up a little bit
So let's do a new measure over here and we're gonna say max month again
Our goal is to be able to tell the model that eight August is the maximum month. So we'll go in over here
Perfect, and we'll say max month and I'll say try one. Oops
Try one and what if we just said max of
Month num, right? How would that work? Now? I know that wouldn't work. But let's just see what happens with this formula
Alright so max month and what I'm gonna do is I have my special formulas table and we're gonna place it there and
I'm gonna place it on this matrix and you can see that the max month is returning 12
So it's just you know, so it's just going to each year and saying oh well, yeah, my max month is 12
That's not quite what we're looking for
We want the month num where the current month off tat is zero. So we just want that one specific result
So let's try this again and we're gonna say new measure
max month
So we're gonna try the same thing but we're gonna bring out a magic wand calculator, right
So if calculate and we're gonna say the same thing month num
There we go, right but then this is where the magic wand kind of comes in you can specify additional filters or
functions and the first function we're gonna use is all because we want to clear the calendar filter, even if we are under
2060 you know
We don't want to consider that you want to clear all filters on calendar
So that's the first thing that we do all calendar here and then we're gonna zoom in again, right?
So we're gonna zoom out all calendar and then with zoom in again to the calendar current month offset is zero
So let's say curl month offset. Perfect equals zero and that's it
Now you can you can try it without all and see how it would work. Okay, I didn't need that and
Let's see the result of this
So again bingo so 8th August so it goes to the most recent, you know
It goes to the current amount offset again based on our ultimate calendar table and picks out the month
Um for that so now we have the month num now all we have to tell power bi is - yeah
Yeah, you know you have a month
Um, right buddy, go ahead and when you're calculating the sales only calculate with a month numb up to that month numb, right?
So we're a month is less than or equal to that maximum simple, right?
So again think about think break break down how human would solve a kind of step by step and then we're teaching at power bi
In a step by step as well. So we have a
ignore the maximum
try one but we're gonna focus on on the second one this result and use that so I'm gonna say a new measure and
We'll say let's say sales
across
Prior years and I mean days are not using the year-to-date language because it has a very different connotation in
Power bi generally and it has that built in ear to date functions. So I'm just saying sales across prior years
I mean if you want you can use the year-to-date function, right? So again, so we're gonna say calculate to actually change my mind
We're we're gonna start by getting the value off the max month
So we we're gonna define a variable max month and just reference the max month measure we're doing and and just as a test
I often like to just return
Something really quick and just to see that it's working
Now, of course it is I expect it to work but you know, I like kind of stressful you said okay. It's already there
So you can see that it has it's returning the same result
So it's not quite sales yet, but we're really really close to it
So get into action here and let's see if we can solve this problem. So
Again, so now I'm going to bring out my magic wand alkylate and again, the logic is simple
I've already taught the human. I broke it down like what the steps the human would take and I'm teaching power
We are the same steps and say hey calculate sales and the only thing you have to remember is not to go beyond the max
Month only go to the max month and I've already figured out what the maximum is. So that one becomes so easy
So we say calendar month, um is less than equal to
max month and
That is that should do it my friends. Let's check the result sales across prior years. It's calculating looks great
Let's put some formatting in here
We're gonna make it shine a little bit so currency
0 and and yeah, you can I'll let you do the math
but yeah, if you were to add up January through August this would add up to exactly
4 point 4 1 3 million and same thing for
2017 now a few things here now for so again
this is just solution 1 wait till you see the second solution and
then you can decide which one is easier which one you would prefer a
Few things here for one. Did you realize that if I hadn't used the variable that it wouldn't work?
Do you know what error we would get if you do know?
That Y variable is used and why not using a variable will not work drop in a comment
Let me know or go ahead and give it a try and let me know what happened
Right, so so that part is important
Now the next thing is not I like building step by step Lego block as in Lego block. You don't you don't necessarily
Need the max month to be a separate variable. In fact, we can define a new version of this
so we can
Copy the same measure go over here
new measure and let's say V 2 V 2 and
Instead of instead of having to define that in a separate variable
what we can do is I'm gonna copy that formula from
my other feet and
Just put that in right?
So you saw what we did instead of building it kind of in two variables
We can choose to do it in a single variable
That's totally up to you and they both of course would give you the same result
But this was the solution one, which was kind of a Dax approach of doing it
There is a totally different way to solve it using
Leveraging the query editor and adding on to our ultimate calendar table and and that's why
It is called the Ottoman calendar table because not only is it is it chock full of goodies?
But it's really easy to customize so for this challenge
I wanted to take another shot at it and let me just give you kind of the final solution show you what it looks like
so here
In the alternate solution what we have is we have a flag in our table which
Marks, what is year-to-date?
So let's say let me clear this flag for a second so you can see
and again
This is being done in the query editor and what I'll let you do is that I'll let you download the file from this link
And that way you can examine exactly how that column is being built. In fact, it is not just that column
I also added a few new things to the ultimate calendar table, which is the not just in year to date flag
But quarter to date and month to date so it's going to give you a lot more flexibility
So now so this measure that I'm using right now is actually my sales measure if I'm not wrong. Let's go take a look
so this is
Sales measure sum of sales sales man, right that that's it
That's the extent of Dax on this page, but given the strength of the ultimate calendar table
What do you need to do so that you get the same apples to apples comparison?
You need to drag and make a slicer out of that
That flag here the date field built into your calendar table
You can just copy paste and use that ultimate calendar and you're gonna click on this one button
And this is the exact same result we used. Yeah, we got using the Dax approach. So what do you think my friends?
Who which?
Approach is better or which one would you prefer and of course there could be other fun steps that you play with you can change
The measure to only use completed months. So in that you could you're gonna use current melt offset
It's minus 1 instead of 0 that we used
You could change the measure to work at a daily level not just in a month level
So go ahead and try it out. And of course an easy way to get started on that would be
Just to download the file and go from there
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