馃攳
How to calculate MTD QTD YTD in Power BI DAX Tutorial (6/50) - YouTube
Channel: Analytics with Nags
[20]
you
[23]
welcome to analytics with nags in this
video we are going to see about a time
[28]
intelligent function MTD qtd and YTD
index we are exploring power bi using a
[38]
business scenario nagas garments for
this business he has some questions and
[43]
we are trying to find answers using a
power bi and with help of tax or power
[49]
curry whatever whichever is applicable
today he got a question like what is my
[54]
cumulative sales quantity over month
quarter year and we can achieve it using
[61]
MTD qtd YTD and also he wants to know
what is my financial year cumulative
[67]
sales if you haven't subscribed this
channel it hit the button right now to
[73]
learn the concepts in power bi let's try
to find answers in our power bi demo so
[88]
we are in power bi now so i have a
simple model where you have a sales fact
[95]
table and you have to dimension tables
for his product and another is dim table
[100]
so i always recommend to have a
dedicated date dimension if you don't
[108]
know why just watch my previous video
uploaded about date dimension creating a
[116]
date table so today's topic MTD qtd and
YTD first starts with MTD so what is MTD
[127]
if you have this quantity like each day
how many quantity it has sold so
[132]
cumulative means on day forth it is
nothing but day 1 day 2 day 3 day 4
[140]
these 4 total is together is considered
as a cumulative total 4 day 4 and what
[148]
is empty readin so MTD is nothing but
for this January month from the day one
[154]
till
thirty first okay and what is my total
[160]
that is what my MTD is all about and as
soon as February first comes the MTD
[169]
we'll start from here for this month let
us see it in action let's try to create
[175]
one measure so sales quantity MTD so for
that we have a measure called total MTD
[195]
okay MTD open braces and what you want
so I want some of quantity the
[204]
expression is nothing but your
aggregation or some kind of calculations
[209]
that you want to perform I want some of
quantity sales quantity and what you
[216]
need to mention it here that date column
that calculates that evaluates that MTD
[224]
function okay so the date function is
nothing but date column okay I need to
[233]
mention the date dot date and I didn't
click that Tim diet as sir date
[239]
dimension so I need to choose this
particular column I will create a
[243]
separate video for it why you need to
choose just like this so now what we are
[247]
saying in total MTD is you calculate
total quantity and use this dim date
[253]
date column since the sales table and
aimed a table is connected using this
[258]
straight column so sales quantity and I
will make this as sales table as my
[263]
phone table then hit enter let us view
this MTD measure in our table sales MTD
[283]
there you go if you see here
as I said 4/4 it is 6 plus 6 is 12 and
[291]
12 plus 6 is 18 and 18 plus 8 is 26 so
like that every day that turtle is
[299]
cumulative every day individual quantity
sells or quantities sold is added okay
[308]
that is what he was saying about
cumulative and MTD as I said when you
[313]
see on January 31st till January 31st
it is 193 in the total sales on
[321]
January's 193 when it comes to February
you see here immediately it starts from
[329]
this month the beginning of the month
that is month to date that is MTD
[334]
function this is your cumulative monthly
figures or quantity sold I hope you
[341]
understand what is MTD so obviously if
you already guessed the Qt is nothing
[349]
but from the date from the beginning of
that January the each quarter is
[354]
constrast three month right so from the
January February month till I mean much
[359]
till March 31st your cumulative data
will be added then beginning of April it
[368]
will be new data okay let's see by
creating a Mishra measure so I will say
[378]
sales quantity qtd
for that I need to use total qtd then
[392]
sum of quantity common quantity should
be table and column name then specify
[405]
the date okay
dates Tim date you need to
[416]
the some Tim dated
[429]
okay hope you got this one let us hit
enter then I'll say qtd let's touch this
[444]
table okay now we already see about this
let us try to have some filter so that
[453]
it will be easy to understand I have
earlier I'll make this list okay so
[468]
2017 and you see that MTD is moving and
it ends at month 1 194 and your QTL so
[479]
it's same but as soon as February shots
this again restarted whereas your qtd it
[486]
continues till quarter end so let us go
quickly to our much end so there you go
[495]
you see here this is the end of MTD and
here the qtd ends here again it begins
[503]
from the next quarter so I hope you
understand about qtd let us quickly
[512]
understand about why TD so why TD is
nothing but till the beginning of the
[518]
year and till the end of the year the
cumulative total should follow and if
[525]
the next year Falls it should start from
the count should shot from the next year
[530]
that is what YTD is all about let us
create it so I want to
[537]
show a new way let's skip this one so
it's not all about you need to write Dax
[544]
always so let me right click it and say
like prompt and we can create a new
[554]
measure over there
[559]
so here is a window and what you want to
do so lot of quick measures can be
[567]
created using people those who are not
familiar with writing Dax they can
[572]
choose this quick measure option now in
this case we want year to date so here
[576]
to date function what calculation you
want as I right click on the quantity it
[581]
is giving me default aggregation sum of
quantity and you need to choose your
[585]
date calculation okay so I will go for
this date ok then click on OK so it has
[599]
created me QT quantity by TD let us see
the data in this table ok so it started
[610]
from here till the air and it will go
that we know let's remove these two and
[618]
try to understand it in graph so when
you see this quantity and quantity YTD
[627]
at ear level both will be same because
the qtt of ITT is nothing but your
[633]
starting of the year till the year what
is my total its cumulative but this one
[637]
aggregates each and every day and show
it at the air level so both will be at
[641]
this same data now let us drill down to
next level now you see the difference so
[653]
this green is your quantity and that is
each month whereas your quantity YTD is
[661]
month on month data I mean the February
data is January plus February March Mon
[668]
data is January for very much
so like that so now you can see the
[673]
growth I mean your business is doing
good in terms of sales okay if it is
[683]
flattened okay now in kovat case people
are saying we need flattened meaning
[688]
there should not be any new but cases
happen in this case if it is happening
[693]
flattened in this case it is washed
because his sales is not happening in
[698]
this case he dashed to be gradually
growing so that is what he is trying to
[703]
analyze using this YTD measure okay so
let us see considering to here let me
[710]
remove this quantity so now you see he
can able to compare the 2017 and 2018 in
[720]
the same graph I mean it is not the
right way to analyze but I'm trying to
[725]
show you how you can see the difference
of YT Domitian now if you ask me like
[732]
second question he has asked like we
have answered all his first question
[738]
whatever I mean quantity of over month
quarter here now coming to his second
[745]
question what is my financial year
cumulative sales so he want cumulative
[749]
sales but this YTD figure that gave me
from the beginning of January but
[755]
usually some people are most of the
businesses wants to analyze from the
[760]
starting of the financially that is from
April or in some European countries it
[767]
will be from I mean end off for your
financially it is much and some
[773]
countries it will be June depending upon
your financial year now for that case I
[781]
need to write a measure I mean
similar measure that we created for YTD
[789]
so let us name it as sales quantity YTD
financial here okay and total YTD
[807]
expression is sum of quantity let me
write it quickly and dimmed 8.8 date now
[822]
here you see the option like year end
date forget about this filter total YT
[828]
the expression is sum of quantity dates
its date column and filter we don't have
[833]
any filter now an ear end so that gets a
catch here so you can specify when is
[840]
your ear end so my ear end is 3 comma 31
that is financially ends okay so instead
[849]
of calculating the beginning considering
beginning of the January it will it will
[854]
automatically consider the beginning of
the year as April
[857]
so my cumulative will start from April
when you use this pressure let us see
[862]
that so I am copying this chart and
putting it below I'm removing this
[873]
quantity by TD instead I will go for
financial ear now you see here you are
[883]
financially or YTD the cumulative total
starts from April till the financial
[888]
layer much this is the main difference
between YTD with the calendar year and
[894]
YTD with the fiscal year specification
that is you need to specify which is
[899]
your end of your fiscal year in this
case it is 31st much I hope you guys
[907]
like this video if you like this video
share and subscribe to the channel
[911]
comment below for your curries do
remember that data is your asset
[928]
you
Most Recent Videos:
You can go back to the homepage right here: Homepage