Power BI Show Year-to-Date (YTD) Across Multiple Years - YouTube

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