馃攳
Power BI DAX Tutorial (8/50) - How to Compare Previous Month Quarter Year Values - YouTube
Channel: Analytics with Nags
[23]
previous year
previous quarter are month values
[28]
index
we are exploring power bi using a
[33]
business scenario naga garments
and today for this business
[39]
we are going to identify how his
products performing compared to last
[45]
month meaning like
how much sales happen compared to last
[49]
month
and how much percentage increase or
[54]
decrease in sales
that is what we are going to see using
[58]
the dax functions
[61]
if you haven't subscribed this channel
yet hit the button
[65]
right now to learn the concepts in power
bi
[69]
let's see it in demo
[74]
so we have this model
already we have seen it we have a sales
[80]
product and team date team date
and product is connected to the sales
[84]
now let us try to write um
previous months before
[90]
before that let us try to see the sales
by date okay so
[98]
let me have this hierarchy here
[103]
okay let us see it
by table
[112]
i don't want a date let's focus on month
okay let me increase the size
[120]
a little bit bear with me
[131]
14 that is good to see and column
address
[135]
that is also good to see 14.
okay so this is our monthly sales
[143]
for 2016-17 for here we want to
calculate
[148]
his previous month sales okay
and step by step will go so first
[154]
preview create previous month sales
[159]
then you need to specify previous
see there are a lot of functions
[164]
previous day previous month previous
quarter previous year
[167]
everything is similar let us start with
previous month
[171]
how this will be useful for this
business previous month
[175]
you need to specify the dim date column
team date dot
[179]
date so that's all it is
as simple as that we can see calculate
[186]
sum of sales amount previous month your
date okay
[189]
let's hit enter
so just change home table that is sales
[199]
okay it's working on it okay
fine now i want previous
[207]
sales okay this is the test measure
created
[211]
it's okay so i want here
in this table
[217]
okay now you see you can see the data
here
[223]
so for 2016 january this is my sales
and for february the sales for this
[229]
previous month is
created now the actual thing is like
[234]
he wants to see a product right i mean
if you see the question here for the
[239]
business user
[243]
to create a percentage difference for
that
[248]
what we can do is the sales amount minus
previous sales amount so
[255]
divided by previous sales amount that
will give us some percentage okay
[259]
let us try to calculate
new measure
[268]
so difference okay okay
[275]
that is some of
okay minus
[286]
sum of no need of sum
already we have calculated just try to
[292]
write
previous month
[297]
okay then
[302]
total this total okay
[308]
divided by
[314]
sum of
[320]
sales amount
[326]
okay let's have this percentage
difference in the graph
[332]
okay maybe this formula might be
slightly
[336]
uh different uh so the previous so it is
negative correct that is formula is
[342]
correct because you see
my current month sales is 146 but my
[346]
previous
month sales is 165 so there is a
[350]
decrease in sales compared to previous
year that is 13 percentage
[354]
so let me move it to sales table
and make it as percentage
[365]
and you can see here there is an
increase
[368]
uh in this case where is that 2.3 so
his previous month sales is 1.161
[374]
and here it is 165. so now he wants to
see
[379]
how much different it happened
across a product right so for that how
[386]
he needs to analyze so let us have
a product here in
[393]
table once again okay
product let us have it as a product
[399]
category let me format paint
same as here so that you can see it
[403]
better
and just you can use percentage
[409]
difference here oh no
hold on
[415]
okay
[418]
maybe i want to see
[424]
previous month sales
with the sales
[434]
amount
okay now why you got hundred percentage
[439]
here is like
you don't have any filters you see here
[442]
that is the issue
so percentage
[447]
difference see you do not filter any
date here for which period it will
[453]
calculate previous period here you see
each row act as a filter context i hope
[459]
i explained more on this filter context
now this time calculation function
[466]
which work closely with the time or date
column so here for each row it acts as a
[472]
filter context so you got the value
whereas here you don't find any value so
[477]
let us try to filter one particular
month
[482]
now you got percentage difference you
see here
[486]
so his previous
sales let us see i mean to say
[494]
for cash flow veer his sales amount is
eight eight seven zero zero and
[500]
his um previous month sales is eight
eight six zero so that is uh
[507]
percentage difference is 0.11 percentage
that is
[511]
increase whereas for this product it is
decreased
[515]
compared to previous month i hope you
understand this context
[520]
if the the time intelligence function
works closely with
[525]
your date okay that is the thing
if you uncheck it you will lose that
[531]
difference because it is
it doesn't make sense okay so you need
[535]
to do some filtering here
so for each month you can see
[541]
what this is different compared to
previous year
[545]
previous i mean previous month so we
achieved
[549]
the end user objective that is he can
able to compare his product
[554]
with his previous month whether it's
increase or decrease
[559]
so similarly we can make
quarterly i mean previous quarter
[566]
previous year
that is obviously you need to use the
[569]
same formula
let's try to create quickly and see it
[573]
in action
i'll make it very quick
[582]
measure tools home new measure
[589]
so as you guys expected it is same
instead of previous month you need to
[595]
use
[597]
and meters quarter
okay and just press enter that's it
[608]
and let us try to create um
one more measure that is previous year
[622]
so previous here
okay as simple as that
[632]
okay let us keep the data over here
let me remove month and you can see
[640]
right now so for the previous quarter
okay this is the value and you have the
[646]
the q4
previous quarter is nothing but q3 data
[651]
okay that is how it works so the same
formula applies for
[656]
your product i mean you can analyze
for a product so instead of sales amount
[662]
previous month
you can have sales amount okay and
[667]
this is for entire period no filters
applied right now
[671]
so let me put a quarter in this
the difference is
[678]
okay now i need to touch some quarter so
that it will get the previous quarter
[682]
you can choose
two okay now you see the data
[689]
so this is your previous quarter data
37200 right
[693]
so if i choose the q1 this
data will shift here because this is
[698]
your previous quarter let's see that
so 372 exactly shifted
[705]
so you see the magic i hope based on
this
[709]
you can create another measure for
quarterly difference
[712]
since your monthly difference measure is
see the percentage difference what you
[718]
have written is like
you used monthly measure
[722]
similarly if you want to go for
quarterly
[726]
percentage difference you need to use
here
[729]
your quarterly measure you need to
create separate measure for it
[733]
okay so that is the point i want to make
obvious
[737]
year so let us try to analyze it
and there you go previous year let us
[744]
try to remove quarter now
let us remove the quarter figures so
[749]
now you see uh the previous year
for that you need a sales amount to be
[755]
viewed
so only um if you view by previous year
[760]
you cannot be able to unless much okay
so
[763]
the sales amount is this one and it
should be
[767]
this way okay the sales amount for 2016
is this and you get
[772]
over previous year the vice versa okay
for 2016 there is no previous year
[779]
because the data starts from 2016
january
[784]
i hope you guys uh understand uh this
concept behind it the previous period
[790]
for month
quarter and year and how it can be used
[793]
for percentage
difference across product or any other
[797]
dimension
if you guys like this video share and
[800]
subscribe to the channel
comment below for queries do remember
[804]
that
data is your asset
[814]
you
Most Recent Videos:
You can go back to the homepage right here: Homepage