Thanks for your concern. Hope this channel is visible to all data analytics learners soon Share this Channel with your friends and colleagues. Thanks once again
Hello, when i recreated the same scenario i am facing an issue. my sales quantity MTD measure when i drag it to the chart it is coming as blank. Sale Qty MTD = TOTALMTD(sum(Sales[Quantity]),Calender_Lookup[Date].[Date]) relation between the sales and calender_lookup is also established. when i remove the date hierarchy and place year, month name and day column to the chart then this sales quantity MTD measure doesn't evaluate. when i add date itself, the measure works like a charm. is this happening because i selected .[Date] in the measure formula but in the chart it is Day, month name and year and no Date as a column under the Value section. i am unable to figure out the reason. Could you please help explain why it is happening ?
I have tried MTD on Financial sample data. the table itself has a date column. So I didn't create a separate date table. but I am getting the same sales total number for each month. not getting cummulative totals. y is it so?
by using seperate calander table i'm trying to cal MTD, i don't have any data from jan 1-jan 6 and jan 10-jan 15 remaining days i have data.in table visualisation Quantiy column those dates are showing null and in MTD column data repeated in those nulls data row so i don't want show nulls data and i want only whatever dates data i have and shows those dates data quanty without showing Nulls and those dates data MTD without repeated data .i hope you understand my question? please clarify my doubt.
If we are having MTD till today ie. 12th Dec 2020 and we have to compare MTD with same no. Of days with previous year 12 Dec 2019.... How can I do that??
you can download online, database/powerbi/ssis/ssas/scripts/azure/app everything under here - - > github.com/microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers
I want to to compare WTD growth to last week growth for same days.(Monday to Sunday) for ex if as if today on 5th may . week started from Monday i.e 2nd may so I want to compare 2 to 5th may (total 4 days) sales to 25th to 28th april (total 4 days). what should I do in this case and it should be dynamic .Thank you in advance :D
In Data table DATE is in '01-january-2016' format and while creating DimDate table we are giving #date(2016,01,01). both the date fields are in Diff format values .can you explain it.
When I am using same table date field that time TOTALMTD not working, When I crate date table and relationship that time it’s working fine We can’t use same table date field ? Could assist me
Hi nagendira, as you have shown FYQTD it cumulated from April but visual is showing from January can you tell me how to start it from April (or whatever financial month starting)
For QTD there is no financial year. I hope you are asking about FY YTD . FY 2017 is from Apr 2016 till Mar 2017. The data I have starting from Jan 2017. So for FY 2017 the YTD started from Jan2017 and ends at mar 2017. Again the YTD starts from Apr 2017 ...Watch second chart at the bottom ...after 14.30 secs
Thank you for the detailed explanation on the concept. I am new to PowerBI and I do have one question on this video. On the table the totals for MTD, QTD and YTD are not appearing on your chart. I followed the steps as explained but the totals are appearing, tried formatting through visuals but does not work. Could you please let me know if I have done something wrong?
Yes. It is good practise to use dedicated date dimension. Not only for calculations, if you have multiple facts having common date dimension between fact is a mandatory Design principle ua-cam.com/video/BnqtBxGbo0M/v-deo.html
Thank you for this series, its really helpful! One question - In "Sales Qty YTD FY", date is specified as DimDate[Date].[Date] - Can you please elaborate what each subpart stands for, is it schema[table].[column]?
Hello Sir, In DIMDATE table as we created the dates manually.... what is the start date and end date need to choose here sir... As sales table has dates from 2016 jan to 2018 dec..... so same dates we need to create in DIMDATE table
please help me when i open the source file in BI and click on model view i cant find sales table for that reason im stuck please help me how to find it and practise asap sir
Dear Nags I have problem in getting the accurate TOTALMTD and I believe it got something to do with my dimensions date table which is end on 31st Dec 2021 while I am trying to get the TOTALMTD for 9th Aug 2021, as it only works for TOTALYTD, but not TOTALMTD nor TOTALQTD. I tried to add the filter e.g. TotalMTD(EXPRESSION, calendar(date), filter (calendar, calendar(date)
Really appreciate your efforts for the level of content for the power bi aspirants. Just a question how do you create dim date component ?.Could not find in the video.
Hello sir. I am following along your dax tutorial playlist. In this video, I am stuck, I watched your date dimension video and created the date table for this dataset and created table visualization, I selected the year, month name and day columns from my date dim table, but the moment I select quantity column from data table, it shows 6555 (sum of quantity) and nothing in year, month name and day column in the table visualization. How do I get it to show quantity for individual rows instead of summarizing it. When I click on don't summarize option for quantity, it gives error in visualization. What to do? Please help, your content and teaching methodology is great and I want to finish this dax playlist.
Watch what is .date in date table / dimension video. That may help. When you stuck, no need to stop here continue with other videos. Just understand the concepts
Ma'am even i have same problem , i have mailed to the admin of this vedio , still no response .. I have created Date.Dim in_query code from google , when i started to import Quantity from the sales(dataset) .. its showing 6555 iterative problem .. Now how to fix ?? big problem
can you plz do on a video of LTM last twelve months if i click on 2016 march it shown (2016 march - 2015 april) if i click 2017 November then the measur have to show last 12 months from 2017 nov like (2016 dec-2017 nov) plz do thevideo on this task
>Hello Naga, thank you for sharing your knowledge my friend.. wondering if you can help me with an attrition measure. I'm calculating a YTD Attrition and for that I need the total drop offs per year (I already have it using the YTD quick measure and your explanation), however I also need the average active HC, kinnda like a YTD average or some sorta, but I´m not able to find it and the "avg option" is not available under the quick measure label. Thank you again bro. kuddos from Colombia
Brother If Growth percentage is showing -100% for previous year with no data how do I show it as blank. For example year 2019 has no data and i want to see growth percentage of 2021 and 2020 till June 21.
Can anyone explain why are you using DimDate[Date].[Date] in all measure. I m not able to use it in. when I try to type it, it shows error. Can someone explain. He said he will do a separate video for this. If you can pls share it
Here is the link to the separate video on .date What is .Date of Date Column in DAX Calculation | Power BI Tutorial (50/50) ua-cam.com/video/PP23ws8H0XE/v-deo.html
I'm getting error while I'm writing Dimdate[date].[Date],,,, error is: Column reference to 'DateKey' in table 'DimDate' cannot be used with a variation 'Date' because it does not have any.
@@AnalyticswithNags Sir it is showing error as Date add only works with contiguous date selections , I had used same period last year function for that also it is showing same error. Kindly help me to resolve this.
sir i tried using MTD = CALCULATE([Total sales],DATESMTD(financials[Date])) then it gave the different answer when i did with MTD1 = TOTALMTD([Total sales], financials[Date].[Date]) what is the difference between these two measures
In the below video ,the difference between totalytd and datesytd are explained. ua-cam.com/video/AFyaYX4Mnwg/v-deo.html Same difference applicable for Mrs as well
Hi sir, Thank you for such valuable content! I had a doubt, as for 2nd question of Financial year, we skipped 'Filter' part in TOTALYTD DAX, so isn't it necessary to add two commas before writing "3/31"?
Hello nags, could you please tell me regarding the formula for dim date table or plse let me know for how many years you created the table dimdate ?? . egarly waiting for reply
i don't know why my PBI don't permit to type Dimdate[Date].[Date] , it's only show Dimdate[Date]. and it also not cumulative :(( (I'm using Tabula model and Dimdate[Date] as date table )
If we are not creating the date table and link it to fact and mark the date table as date..it will create individual date table in power bi....In this case you no need to use Sale.saledate.date instead use dimdate.date.
Dimdate is used for all Timeintelligence function. watch next 10 videos from relationship video in the complete playlist. Relation ship video ua-cam.com/video/rdscvHteaCw/v-deo.html Complete Playlist ua-cam.com/play/PLjNd3r1KLjQt0xN_y8F6BSIOVNvdQmq4d.html
Hi sir, actually my requirement is let's say we have a Country and Year&Month slicers, when i select India it should take the FY as 1st Apr to 31st March and when i select US it should take the their country financial Year.So that the year end date should change dynamically in the TotalYTD DAX or DatesYTD DAX, so is that possible? So can pls do the video on this ASAP in the next week before 31st Dec 2020 if possible for u?
That is very easy to understand consider this is the calendar year first day starts on January 1st. But, our company fiscal year is 445 calendar, first day starts in December. So, I have to create a calendar table with start and end date. If I want to create MTD, QTD and YTD measure, would the concept still be the same? Thank you so much for sharing.
YTD works when you have standard fiscal year of 365 days and it can start any month. i.e either April or July. In your case you have 445 days, for that you need to create a logic that contains the Fiscal year column in calendar Table then use this new column for your YTD calculations. For QTD / MTD I hope you don't find any issues your existing calculations works fine. Try yourself , i will soon make video on this.
@@AnalyticswithNags Thank you for your comments. Yes, I have been trying this for months and searched everywhere including the Power Bi Community, but I just can't find one that I understand. I mean, I don't want to just copy and paste their code, I want to understand it. There is one UA-cam I found that I have been watching for several times and I understand exactly what he did; however, I don't understand the parameter part. I followed his steps through but my years are different. Our fiscal 2021 started on December 20, 2020. Here is the UA-cam in case you are interested. ua-cam.com/video/Q7jC1pEulis/v-deo.html I'm looking forward to your video on the 445 calendar. Thank you again!
Hi sir, actually my requirement is let's say we have a Country and Year&Month slicers, when i select India it should take the FY as 1st Apr to 31st March and when i select US it should take the their country financial Year.So that the year end date should change dynamically in the TotalYTD DAX or DatesYTD DAX, so is that possible? So can pls do the video on this ASAP in the next week before 31st Dec 2020 if possible for u? Could you please let us know how we can do this??
Usually Financial year is Fixed for Business. It seems like Interview tricky Question. My suggestion is, If you have country dimension table add the Financial Year end column against each country. In DAX YTD measure , Use variable to get the Financial year end based on country selected. Then use this variable in the YTD DAX calculation at Financial year end. Try this. If time permits i will make one video on this
I have been struggling for a month and this video has help me understand in a rather different way. Thank you and may your videos excel each time.
Thanks keep learning. Share this channel with friends and colleagues
Great😃.. Today I have almost cleared the confusion of DAX by watching your 5 videos only.. thank you for the amazing content.., more to go...
Thanks keep learning share this channel with friends and colleague
most underrated channel on youtube interms of power bi learning sir you are great nobody is giving the live project keep it up a lot of love -priya
Thanks for your concern. Hope this channel is visible to all data analytics learners soon
Share this Channel with your friends and colleagues.
Thanks once again
Have been reading book written by MS guy for DAX. But IMO this is the best dax content i have seeen so far.
Please share this content with your friends and colleagues
Simple snd clean explanation. I started learning DAX. Thank you
1) MTD: TotalMTD(SUM(Sales[Quantity]), DimDate[Date].[Date]) #You can also use quick measure for this
2) YTD: TotalY TD(SUM(Sales[Quantity]), DimDate[Date].[Date])
3) QTD: TotalQTD(SUM(Sales[Quantity]), DimDate[Date].[Date])
4) YTD by financial year: YTD: TotalY TD(SUM(Sales[Quantity]), DimDate[Date].[Date], "3/31")
Thank You . Excellent Video ...
+ Financial Year explanation also make this lesson interesting further...
Thank You
Thanks
Thank you for the clear explanation, keep going. You are doing great work.👍
Thank you, I will
Easy to understand and good simple content. Thanks for sharing knowledge.
Glad it was helpful! Keep learning and Sharing.
Hello,
when i recreated the same scenario i am facing an issue. my sales quantity MTD measure when i drag it to the chart it is coming as blank.
Sale Qty MTD = TOTALMTD(sum(Sales[Quantity]),Calender_Lookup[Date].[Date])
relation between the sales and calender_lookup is also established.
when i remove the date hierarchy and place year, month name and day column to the chart then this sales quantity MTD measure doesn't evaluate.
when i add date itself, the measure works like a charm. is this happening because i selected .[Date] in the measure formula but in the chart it is Day, month name and year and no Date as a column under the Value section. i am unable to figure out the reason.
Could you please help explain why it is happening ?
To understand what does . Date does, watch this video ua-cam.com/video/PP23ws8H0XE/v-deo.html then you will understand what is going wrong
@@AnalyticswithNags The doubt was clear. Thank You.
simple but powerful tips. fiscal year tip was bonus.
Thanks keep learning share this channel with friends and colleagues
Where do I find your video on creating date tables?
I have tried MTD on Financial sample data. the table itself has a date column. So I didn't create a separate date table. but I am getting the same sales total number for each month. not getting cummulative totals. y is it so?
Use .date Dimdate[date].date
by using seperate calander table i'm trying to cal MTD, i don't have any data from jan 1-jan 6 and jan 10-jan 15 remaining days i have data.in table visualisation Quantiy column those dates are showing null and in MTD column data repeated in those nulls data row so i don't want show nulls data and i want only whatever dates data i have and shows those dates data quanty without showing Nulls and those dates data MTD without repeated data .i hope you understand my question? please clarify my doubt.
Use If condition before measure
If we are having MTD till today ie. 12th Dec 2020 and we have to compare MTD with same no. Of days with previous year 12 Dec 2019....
How can I do that??
Use calculate ( total YTD , date add) this should work
Thank you, already subscribed to your channel. First time using MTD. Will try YTD as well. Mike - Melbourne Au.
Awesome, thank you!
Really i didn't understood this topic in past but now i got clear idea on it really thank you sir ,iam clear now😊
thank you , keep learning
Thankyou sir, All doubts are cleared.
Always welcome
do we have to use a new data file for this video or else the file that we downloaded in the first video ????????
you can download online, database/powerbi/ssis/ssas/scripts/azure/app everything under here - - > github.com/microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers
What is the name of the video which explains why DimDate[date].[date] syntax?
Check this out.
What is .Date of Date Column in DAX Calculation | Power BI Tutorial (50/50)
ua-cam.com/video/PP23ws8H0XE/v-deo.html
I want to to compare WTD growth to last week growth for same days.(Monday to Sunday) for ex if as if today on 5th may . week started from Monday i.e 2nd may so I want to compare 2 to 5th may (total 4 days) sales to 25th to 28th april (total 4 days). what should I do in this case and it should be dynamic .Thank you in advance :D
Soon I will make a video on this
In Data table DATE is in '01-january-2016' format and while creating DimDate table we are giving #date(2016,01,01). both the date fields are in Diff format values .can you explain it.
When I am using same table date field that time TOTALMTD not working,
When I crate date table and relationship that time it’s working fine
We can’t use same table date field ?
Could assist me
How quantity is taken in table , in my case it is showing sum of quantities.
Not sure about your question
Hi, I just want to know how did you get the DimDate table since in the dataset provided in the description box doesn't have it
ua-cam.com/video/BnqtBxGbo0M/v-deo.html
Hi nagendira, as you have shown FYQTD it cumulated from April but visual is showing from January can you tell me how to start it from April (or whatever financial month starting)
For QTD there is no financial year. I hope you are asking about FY YTD . FY 2017 is from Apr 2016 till Mar 2017. The data I have starting from Jan 2017. So for FY 2017 the YTD started from Jan2017 and ends at mar 2017. Again the YTD starts from Apr 2017 ...Watch second chart at the bottom ...after 14.30 secs
@@nagendrakg5062 ok thank you
Thank you
Thank you for the detailed explanation on the concept. I am new to PowerBI and I do have one question on this video. On the table the totals for MTD, QTD and YTD are not appearing on your chart. I followed the steps as explained but the totals are appearing, tried formatting through visuals but does not work. Could you please let me know if I have done something wrong?
That’s ok. Until your MTD calculation works fine. Surely it is some format option even I don’t remember
It was really a well explained video thank you so much .
Thanks keep learning and share this channel with friends and colleagues
@@AnalyticswithNags sure 🙂
Dear Naga, Is there any video where you have shown how to add a calendar on the dashboard which can be used to filter different options.
Check video on DimDate creation
Separate Date table is mandatory to calculate MTD,YTD,QTD? by using fact table sales date we can cal MTD,YTD,QTD?
Yes. It is good practise to use dedicated date dimension.
Not only for calculations, if you have multiple facts having common date dimension between fact is a mandatory Design principle
ua-cam.com/video/BnqtBxGbo0M/v-deo.html
I'm not able to find Sales Quantity Option in DAX Formula
You need to write
@@AnalyticswithNags HELLO SIR IM NEW TO THIS.
can you please tell me how to write sales quantity in this dataset
Thank you for this series, its really helpful!
One question - In "Sales Qty YTD FY", date is specified as DimDate[Date].[Date] - Can you please elaborate what each subpart stands for, is it schema[table].[column]?
Thanks keep learning
Hello Sir,
In DIMDATE table as we created the dates manually.... what is the start date and end date need to choose here sir...
As sales table has dates from 2016 jan to 2018 dec.....
so same dates we need to create in DIMDATE table
yes
please help me when i open the source file in BI and click on model view i cant find sales table for that reason im stuck please help me how to find it and practise asap sir
Not sure.
Thanks keep learning. Share this channel with friends and colleagues
Dear Nags
I have problem in getting the accurate TOTALMTD and I believe it got something to do with my dimensions date table which is end on 31st Dec 2021 while I am trying to get the TOTALMTD for 9th Aug 2021, as it only works for TOTALYTD, but not TOTALMTD nor TOTALQTD. I tried to add the filter e.g. TotalMTD(EXPRESSION, calendar(date), filter (calendar, calendar(date)
Restrict date table with the fact date
@@AnalyticswithNags tks
Really appreciate your efforts for the level of content for the power bi aspirants. Just a question how do you create dim date component ?.Could not find in the video.
Check playlists of this channel and explore topics on the similar line requested by you.
How could i generate a dynamic table of Current/YTD/MAT with multiple selections of year month slicer?
Soon will create video on this topic
Hello sir. I am following along your dax tutorial playlist. In this video, I am stuck, I watched your date dimension video and created the date table for this dataset and created table visualization, I selected the year, month name and day columns from my date dim table, but the moment I select quantity column from data table, it shows 6555 (sum of quantity) and nothing in year, month name and day column in the table visualization. How do I get it to show quantity for individual rows instead of summarizing it. When I click on don't summarize option for quantity, it gives error in visualization. What to do? Please help, your content and teaching methodology is great and I want to finish this dax playlist.
Watch what is .date in date table / dimension video. That may help.
When you stuck, no need to stop here continue with other videos. Just understand the concepts
Ma'am even i have same problem , i have mailed to the admin of this vedio , still no response .. I have created Date.Dim in_query code from google , when i started to import Quantity from the sales(dataset) .. its showing 6555 iterative problem .. Now how to fix ?? big problem
U have explained everything... Can u say how to calculate WTD and previous WTD i m struggling with it.. Thanks in advance.
Soon will make video on this
@@AnalyticswithNags tqsm
can you plz do on a video of LTM last twelve months
if i click on 2016 march it shown (2016 march - 2015 april)
if i click 2017 November then
the measur have to show last 12 months from 2017 nov
like (2016 dec-2017 nov)
plz do thevideo on this task
Answer in this video ua-cam.com/video/hJLOBvn_f8k/v-deo.html
Hello sir, I am getting error while entering DimDate, as if it is not a key command in powerBI
Explore the similar problems here, learn.microsoft.com/en-us/answers/
Thanks for the video. But can you compute the quantity sold by MTD or YTD by Product Name or Product Category. Thank you hoping for feedback.
Yes. Ensure you filter any year/month combination
>Hello Naga, thank you for sharing your knowledge my friend.. wondering if you can help me with an attrition measure. I'm calculating a YTD Attrition and for that I need the total drop offs per year (I already have it using the YTD quick measure and your explanation), however I also need the average active HC, kinnda like a YTD average or some sorta, but I´m not able to find it and the "avg option" is not available under the quick measure label. Thank you again bro. kuddos from Colombia
Sorry don’t have time to reply comments and provide solutions.
keep learning share this channel with friends and colleagues
Brother If Growth percentage is showing -100% for previous year with no data how do I show it as blank. For example year 2019 has no data and i want to see growth percentage of 2021 and 2020 till June 21.
Check the respective measure. Meaning PrevyearTotal is zero then don't show value.
Can anyone explain why are you using DimDate[Date].[Date] in all measure. I m not able to use it in. when I try to type it, it shows error. Can someone explain. He said he will do a separate video for this. If you can pls share it
Here is the link to the separate video on .date
What is .Date of Date Column in DAX Calculation | Power BI Tutorial (50/50)
ua-cam.com/video/PP23ws8H0XE/v-deo.html
@@AnalyticswithNags Thank you
I'm getting error while I'm writing Dimdate[date].[Date],,,, error is: Column reference to 'DateKey' in table 'DimDate' cannot be used with a variation 'Date' because it does not have any.
Explore the similar problems here, learn.microsoft.com/en-us/answers/
Could you pls explain about why u took , Dim date(date).(date)..,please explain clearly , I did not understand
Watch this video
ua-cam.com/video/PP23ws8H0XE/v-deo.html
Now I cleared , Thnak you so much
thanku sir.very helpful
Thanks keep learning share this channel with friends and colleagues
Can we use a measure as a parameter while creating another measure?
Check the “what if analysis”video. Search in channel home page
I have 2017,2018,2019,2020 sales i want calculate only 2018 sales how can do in dax.
Check this video
ua-cam.com/video/UccMggvX2Dw/v-deo.html
How to calculate previous day dispatches?
Use Dax dateadd function
@@AnalyticswithNags Sir it is showing error as Date add only works with contiguous date selections , I had used same period last year function for that also it is showing same error. Kindly help me to resolve this.
IN YTD, how we can filter the date till a specific range, can you please give some example ?
Check the video show data till selected month
sir i tried using MTD = CALCULATE([Total sales],DATESMTD(financials[Date])) then it gave the different answer when i did with MTD1 = TOTALMTD([Total sales], financials[Date].[Date])
what is the difference between these two measures
In the below video ,the difference between totalytd and datesytd are explained. ua-cam.com/video/AFyaYX4Mnwg/v-deo.html
Same difference applicable for Mrs as well
Please make a video with end to end project intermediate to high level also please try to provide data set that you use while making video.
Watch end to end playlist
Great. Thank you
Hi sir,
Thank you for such valuable content!
I had a doubt, as for 2nd question of Financial year, we skipped 'Filter' part in TOTALYTD DAX, so isn't it necessary to add two commas before writing "3/31"?
Yes
@@AnalyticswithNags Sir , sales & dimdate are missing in dataset
Love you sir ❤️🙏😊
Sir why we use dimdate[date].[Date]
I mean why we use date in last bracket
Watch the .date video on channel playlist
Hello nags, could you please tell me regarding the formula for dim date table or plse let me know for how many years you created the table dimdate ?? . egarly waiting for reply
Check power bi Complete playlist
Thank you sir 🙏🏻❤
Most welcome
i don't know why my PBI don't permit to type Dimdate[Date].[Date] , it's only show Dimdate[Date]. and it also not cumulative :(( (I'm using Tabula model and Dimdate[Date] as date table )
Watch the video relate to .date in channel
Hi can I ask one question without SQL can you learn power bi
Yes. If you are business user then no need of sql. If you are looking for a job then you should learn SQL first or parallel while learning power bi
Hi sir ur explanation is very nice plz provide dim date table for practise purpose
sure
Hi Can you share the dimdate table creation video link?
Here is the link... ua-cam.com/video/BnqtBxGbo0M/v-deo.html
@@AnalyticswithNags thanks
Hai I Am new to power bi can please tell me how to create date table based on source table
Refer this.
ua-cam.com/video/BnqtBxGbo0M/v-deo.html
Very nicely explained.
You mentioned you will create a video which explains why dimdate date.date is selected. Please share link of that video.
Thanks
If we are not creating the date table and link it to fact and mark the date table as date..it will create individual date table in power bi....In this case you no need to use Sale.saledate.date instead use dimdate.date.
good question, in fact, I too had the same doubt
@@AnalyticswithNags super
what is the least difference between DATESMTD and TOTALMTD?
Watch this video
ua-cam.com/video/AFyaYX4Mnwg/v-deo.html
Hi na, one doubt here why you did not use calculate function?
These functions doesn't require.
hi sir why we used dimdate table i did not understand that can you please explain requesting you.
Dimdate is used for all Timeintelligence function.
watch next 10 videos from relationship video in the complete playlist.
Relation ship video
ua-cam.com/video/rdscvHteaCw/v-deo.html
Complete Playlist
ua-cam.com/play/PLjNd3r1KLjQt0xN_y8F6BSIOVNvdQmq4d.html
Hi Sir, is it possible to give year end date dynamically in TotalYTD DAX instead of fixed like(4/30), so can u pls help me with this if there is a way
Yes. You can use variable in that year end date and variable should be dynamiCally get the value
@@AnalyticswithNags ,if possible can u pls do that video on that pls, it will help us many people or ping the measure here how it can be.
Sure will do in coming weeks
thanks
Hi sir, actually my requirement is let's say we have a Country and Year&Month slicers, when i select India it should take the FY as 1st Apr to 31st March and when i select US it should take the their country financial Year.So that the year end date should change dynamically in the TotalYTD DAX or DatesYTD DAX, so is that possible? So can pls do the video on this ASAP in the next week before 31st Dec 2020 if possible for u?
Here while calculating ytd quater wise,the month should start from april but it is starting from jan
Filter the fiscal year
That is very easy to understand consider this is the calendar year first day starts on January 1st. But, our company fiscal year is 445 calendar, first day starts in December. So, I have to create a calendar table with start and end date. If I want to create MTD, QTD and YTD measure, would the concept still be the same? Thank you so much for sharing.
YTD works when you have standard fiscal year of 365 days and it can start any month. i.e either April or July.
In your case you have 445 days, for that you need to create a logic that contains the Fiscal year column in calendar Table then use this new column for your YTD calculations.
For QTD / MTD I hope you don't find any issues your existing calculations works fine.
Try yourself , i will soon make video on this.
@@AnalyticswithNags Thank you for your comments. Yes, I have been trying this for months and searched everywhere including the Power Bi Community, but I just can't find one that I understand. I mean, I don't want to just copy and paste their code, I want to understand it. There is one UA-cam I found that I have been watching for several times and I understand exactly what he did; however, I don't understand the parameter part. I followed his steps through but my years are different. Our fiscal 2021 started on December 20, 2020. Here is the UA-cam in case you are interested. ua-cam.com/video/Q7jC1pEulis/v-deo.html
I'm looking forward to your video on the 445 calendar. Thank you again!
where can find DimDate because it is not exist in the dataset
ua-cam.com/video/BnqtBxGbo0M/v-deo.html
How did we get the quantity 6?
Dear Sir, Can you please share date dim table to do this exercise
Dim date is not present in data source sheet. Can you please provide DIM DATE sheet also ?
ua-cam.com/video/BnqtBxGbo0M/v-deo.html
Hi sir, actually my requirement is let's say we have a Country and Year&Month slicers, when i select India it should take the FY as 1st Apr to 31st March and when i select US it should take the their country financial Year.So that the year end date should change dynamically in the TotalYTD DAX or DatesYTD DAX, so is that possible? So can pls do the video on this ASAP in the next week before 31st Dec 2020 if possible for u?
Could you please let us know how we can do this??
Usually Financial year is Fixed for Business. It seems like Interview tricky Question.
My suggestion is,
If you have country dimension table add the Financial Year end column against each country.
In DAX YTD measure , Use variable to get the Financial year end based on country
selected.
Then use this variable in the YTD DAX calculation at Financial year end.
Try this.
If time permits i will make one video on this
Hi naga..have you done video for this query? Might be helpful for me also
Dear Sir...Could you please share the sample data ? so that we can practice.
Check in description
@@AnalyticswithNags Thank you
THANKS NAG
Thanks keep learning share this channel with friends and colleagues
Hello sir, Earlier there was a dedicated playlist for time intelligence functions and now there is none please make that again
sure will try
Please tell how to create this dimdate table?
Check the description
very usefullllll
Thanks. Keep learning.
Share this channel with friends and colleagues
Dimdate option isnt available
Refer Our Date table Video ua-cam.com/video/BnqtBxGbo0M/v-deo.html
I guess the 3rd parameter was the "filter" in the YTD financial year
sum of Total 6555 is showing in every Row of Month. Please help
Relationship missing
your teachining great , can you provide datset link
Thanks keep learning
why are you using .Date in the end ?
Confusing
It is to refer the date column
Hi, can you share your all 50 DAX videos. Here, only have 16 videos.
Keep watching the channel we will upload
@@AnalyticswithNags thank you sir
Sir, how can I get that dimdate table
ua-cam.com/video/BnqtBxGbo0M/v-deo.html this video contains DimDate creation
Very nice
Thanks keep learning
Good Work
Thank you! Cheers!
Sir, please share the dimdate excel file or explain about this - because i stuck here right now, help me sir!
ua-cam.com/video/BnqtBxGbo0M/v-deo.html
How to calculate WTD????
No direct function. Will take the video soon
can you share the ppt's with us
No. Ppt will not be shared
hello sir, im getting mtd value is blank
Mtd alwayd shows the latest month value I think...so populate till current month and try. Otherwise you hav not used proper date column in MTD measure
@@AnalyticswithNags I am showing the same how to do this
Measure not understood in MTD QTD YTD basically
I don’t understand your question
What kind of video, you have uploaded incomplete video. I suggest you to post complete video in one
Thanks for suggestion
What is DIm ? What is DimDate? What is Month & MonthNO, Quarter & QuarterNo?
ua-cam.com/video/BnqtBxGbo0M/v-deo.html
Just watch previous video 😂😂😂😂 that set
Not sure what you are talking about
Content is amazing. But its not clear. Its not legible after zoom in also. Kindly improve it quality so as to share to more people.
Noted