Hello.
So in this lecture we will learn about how we can compare our sales data from this year with the previous
year using a very simple daks formula and then we will see how we can analyze it using conditional formatting.
So we will first take our people here are table visual and in this review and our see is the there we
go and we will add the bits are.
So this is the high tech update.
We just need done on order did.
Let me just zoom it.
You can see the C is the order did.
Let me also make it a bit bigger in those text size.
There we go.
Well now we have done it and this is over all.
Now what we've done is we want to see that last year.
OK.
So for that we have to create a couple of measures.
First measure we will be creating is we will be taking the total sum of the cells.
So the word is equal to
some of our C's in this drop down to select.
Ours is just like that and it did we have to do.
Now we will be using our decks from a very easy and very useful formula.
So what I'm seeing very important and very useful because this can really be our savior for you when
you are comparing the last years since the damage with this.
Yes because with just one formula it would be very easy for you to analyze it.
Even name it lost.
And since last year since so this will be gone.
Now we will be adding our total sales which we have just created tortoise's data
so we can really directly add the two doses that we need to put a condition here so far that we will
be adding that calculate measure to measure the culprit formula helps to and an expression the expression
with total sales data.
And along with that we can offer it to VVA and our scenes to dulcimers measure which I've just created
and we will apply a condition of the major which I am talking about which is same period last year so
just type in the same period last year and now what I need for this year is that our column it's consist
of our dates.
So we have here the order date selected and close the brackets and we are done.
So just with this one particular formula same period last year we have created our last year sales column
as well.
There we go.
So you can see our data is from 2010 to 2017 so this call 2010 will be remain as black and decent next
when news will be there.
So how you can we can say verify this data so you can see for us General 2011.
Our currency is here these days 35000.
And it shows the last two years instead of twenty four thousand seven seventy two dollars.
So as you can see for 2010 it has the same value 24000 772.
So now we have this column by column A comparison of our values of sales this year and last year so
now we want to make it much more visually effective effectively using conditional formatting.
OK.
So let's do the conditional formatting in here.
So for that we need to create one way to measure our audience variants of things of this year.
Minus sales of previous year or last year.
This will be the difference.
So why not let me put this in our table too.
Now there you can see but what there will be one more problem in this particular measure after you will
create it so I can see this is pretty well pretty much showing good data to all this positively or negatively
shows that shows are past profit or loss from DC past year.
But what about this in 2010 there is actually no last year sales.
So it issuing just sales year which doesn't really look good.
So we need to make them blend as well.
So we need to do some of the changes in our formula for that right before the law will add another if
condition.
So if we lose the is blank formula which is also found in a mass exodus.
So if it's if it's blank last year says then our condition should be blank.
All right.
And if it is not long then this condition to us is for us as last year's Superbowl.
And there you go.
Now you can see the variance is blank up here which kind of really makes sense.
Now we need to add the conditional formatting here.
So for that we will jump to format and add the conditional formatting here so we want the background
should be colored with like you are on and we will select our color so we want the coloring on the variance.
Let me turn this one off and make the variance color.
So now I will go to advanced color and I will click Check the color by those to now my condition would
be if the value is greater than 0 than color should be.
Let's see this blue.
We will add one more condition here.
Me this if the value is letter B values less than zero then this is right.
This is our condition for conditional formatting rather than zero value less than zero.
And when you hit OK there you go.
So now you can see our conditional formatting has been applied so so much of we have taken literally
in 2000 and 11.
But as the time passes by we have really gained so much of profit in 2015.
You can see those blue cells right there.
And again in 2017 we are kind of lacking.
So we'll work on our sales.
So this is how you use the conditional formatting and you're saying as of this year versus last year
data to analyze all the values.
So I hope you have understood this.
And again if you have any doubt in the majors which we have created what any of the analysis.
Just feel free to ask it in the gray section and I'm happy to where we will be doing some really.
Another interesting thing in our next video.
So I'll meet you there.
And thank you.