Hi nag, I did'nt understand tha lastdate function. When I search for the function it is said that "it will return lastdate in the current context for the specified column of dates" so it has to return some 2020 dec right? Y it is returning jan of 2016? please clarify this doubt nag
If the data for all month present then you can use 3 directly. But assum out of three months only one month data is used then you need use this kind of calculation. Thanks for pointing out
Hi Naga,just wanted to understand the difference if you would have used count function instead of distinct count . Can you please explain in a detailed way
Count will give you number rows in a table. Distinct count is for a column gives number of distinct values. E.g sales table contains 1000 rows and 5 products. Count(sales(product)) will give 1000 and distinct count(sales(product)) gives 5
Hi sir .... let's say I have created a measure using calculat with applying filters.... now I have show that measure in table ... now I want to calculat % for that I need to devid each value with total... how I can do that
Hi Naga, I want to calculate previous month sales based on selected month. I.e dynamic. Eg. Now we are in Nov-2022, User will select Feb-2022, then I need to show feb-22 and Jan-22 sales to compare Could you please share the dax?
it's a nice demo expert i retry the steps and it works very well : what about adding a slicer visual that contains date_X (format : dd/mm/yyyy , type : list of dates) and trying to have the cumulative active sales_amount : based for my slicer selection (example : date_X = 2023-06-25) i want to have in table visual the sum active_sales_amount per country , only showing country and sum_active ( the sum active_sales_amount should be the cumulative sum between 2023-03-25 and 2023-06-25 , i tried to use datesInPeriod function , doesn't satisfy my need)
Why we used lastdate as start date? And why it took last dat as jan? Why it didn't took lastdate as dim. Dates last date of end. Like 2019/12/1 2 nd query why we cannot take count of month = 3 directly. If we know interval is 3.
If the data for all month present then you can use 3 directly. But assum out of three months only one month data is used then you need use this kind of calculation. Thanks for pointing out
I have one question, still i am unable to understand the part where u used LASTDATe in ur start date. I know it is giving desired result but last date should show last date in column, then how come it is considering first date? Can u explain this please.
Sir I have a scenario that I have 4 year data which start from 1st Feb 2011 and 31 Dec 2014 . I want to show this data from 1 st of April to 31st March in the bar chart. But when I want to click on first financial year I want to see only Feb and March data. And I also want to make slicer like 2011, 2011-12, 2012-13, 2013-14 and so on. So it can be possible?
Hi Sir, I have a question about DimDate. Do i need to create a DimDate table according to my date or can i have 1 universal DimDate table that I can use with all my data? I tried DatesInPeriod function using some random DimDate table that I downloaded from somewhere (it contained dates from 1900-2030) and it considered the LASTDATE of my DimData table which which was not same as last date of my data date (2020 was the last year in my data). What do you suggest? Should the first and last date of the DimDate be same as that of Data (Date)?
That’s true. First date and last date of dim date should not exceed the fact dates. This is 90% of the case. Only case where you will extend the date table is budget for next year and for some predictions if required.
DimDate[Date].[Date] -- why we put .[Date] & why not we put .[Month] or .[Quarter] & What happens we put it. DimDate[Date] & DimDate[Date].[Date] are different OR Similar ? In StartDate - LastDate(DimDate[Date].[Date]) in 2016 Qtr 1 January -means it calculating TotalSales from 31-January-2016 ? and what about TotalSales from 01-January-2016 to 30-January-2016 ?
Hi Sir, Thank you so much for such nice videos. I tried to create a new column using DatesInPeriod but i got an error "A table of multiple values was supplied where a single value was expected". DatesInPeriod is supposed to return a column but why am I not able to create a new column of dates using this function?
Not clear with topics as well as not explining indetail about functions. Suddenly started using variables and don't know why we need to use them. Also dim.date is using form 3rd video but it is almost 10th video and not explained clearly about dim.date.
Dimdate is created using power query. Which will not come understand DAX. Also if it is not available search in the channel any video is available related to that. ruclips.net/video/BnqtBxGbo0M/видео.html Moreover, dimdate can be from database, or created in power query or created using DAX. Soon will create video on DAX Variable is a temporary storage of any programming language. Which is very basic and self explanatory. I understand those don't have programming knowledge dont know this. Will soon make video on this.
![Noob To Max With DRAGON REWORK In Blox Fruits [FULL MOVIE]](http://i.ytimg.com/vi/LnBMOinoOvA/mqdefault.jpg)
Can you explain how the distinctcout(dimdate) function is working here exactly pls?
Hi nag, I did'nt understand tha lastdate function. When I search for the function it is said that "it will return lastdate in the current context for the specified column of dates" so it has to return some 2020 dec right? Y it is returning jan of 2016?
please clarify this doubt nag
For each row Filter context Works. Thats why it shows jan 2016
Same query sir....not understand this row filter context reason. Pls elaborate more.
@@AnalyticswithNags thanks for this explanation.
Hey, Why have we used countmonth variable instead we can simply divide it by 3? I believe it'll give the same input !!
If the data for all month present then you can use 3 directly. But assum out of three months only one month data is used then you need use this kind of calculation.
Thanks for pointing out
@@AnalyticswithNags Thanks for such valuable information. I search similar answer.
Hi Naga,just wanted to understand the difference if you would have used count function instead of distinct count . Can you please explain in a detailed way
Count will give you number rows in a table. Distinct count is for a column gives number of distinct values.
E.g sales table contains 1000 rows and 5 products.
Count(sales(product)) will give 1000 and distinct count(sales(product)) gives 5
Hi..can u explain how to calculate total.sales except the last 2 day sale
In calculate use filter and condition like date < today()-2
@@AnalyticswithNags i tried but still facing issue..
Hello sir , how you arranged your month names accordingly like Jan, Feb ,march ,mine is not arranged in such manner
Keep month column order column
hi sir, where this average value stores i mean when i consider this return value it not shows in table visual
All measures(average In this case) are not stored anywhere. They are return on refreshing the reports or filtering some members
@@AnalyticswithNags tq so much for reply
Hi sir .... let's say I have created a measure using calculat with applying filters.... now I have show that measure in table ... now I want to calculat % for that I need to devid each value with total... how I can do that
Check calculate functions in Dax playlist
We using DAX function / query under measures....?
Or both are different things?
and what is M query?
Watch this you will understand better. ruclips.net/video/lfLAvUARle4/видео.html
Hi Sir, Can you please explain where to use Date & where to use Month. In this Video you are changed the Date to Month (8.52).
It depends on the context, can you explain what is your requirement and what you want to do ?
last 3 months sales excluding the cureent month hw to achieve it
Hi Naga, I want to calculate previous month sales based on selected month. I.e dynamic.
Eg. Now we are in Nov-2022, User will select Feb-2022, then I need to show feb-22 and Jan-22 sales to compare
Could you please share the dax?
Check previous month calculation video/ dateadd function
3:02 for Cumulative moving average you should use AVERAGE instead of SUM😊
yes sure
it's a nice demo expert i retry the steps and it works very well : what about adding a slicer visual that contains date_X (format : dd/mm/yyyy , type : list of dates) and trying to have the cumulative active sales_amount : based for my slicer selection (example : date_X = 2023-06-25) i want to have in table visual the sum active_sales_amount per country , only showing country and sum_active ( the sum active_sales_amount should be the cumulative sum between 2023-03-25 and 2023-06-25 , i tried to use datesInPeriod function , doesn't satisfy my need)
Explore the similar problems here, learn.microsoft.com/en-us/answers/
Why we used lastdate as start date? And why it took last dat as jan? Why it didn't took lastdate as dim. Dates last date of end. Like 2019/12/1
2 nd query why we cannot take count of month = 3 directly. If we know interval is 3.
If the data for all month present then you can use 3 directly. But assum out of three months only one month data is used then you need use this kind of calculation.
Thanks for pointing out
I have one question, still i am unable to understand the part where u used LASTDATe in ur start date. I know it is giving desired result but last date should show last date in column, then how come it is considering first date? Can u explain this please.
That’s true. When you use this against any column in chart filter context apply
Sir I have a scenario that I have 4 year data which start from 1st Feb 2011 and 31 Dec 2014 . I want to show this data from 1 st of April to 31st March in the bar chart. But when I want to click on first financial year I want to see only Feb and March data. And I also want to make slicer like 2011, 2011-12, 2012-13, 2013-14 and so on. So it can be possible?
Explain in detail pls.
Hi Sir, I have a question about DimDate. Do i need to create a DimDate table according to my date or can i have 1 universal DimDate table that I can use with all my data? I tried DatesInPeriod function using some random DimDate table that I downloaded from somewhere (it contained dates from 1900-2030) and it considered the LASTDATE of my DimData table which which was not same as last date of my data date (2020 was the last year in my data). What do you suggest? Should the first and last date of the DimDate be same as that of Data (Date)?
That’s true. First date and last date of dim date should not exceed the fact dates.
This is 90% of the case.
Only case where you will extend the date table is budget for next year and for some predictions if required.
Sir upload more videos on DAX plz
Sure in coming weeks
why use LAstdate, not able to understand. Can anyone help
LASTDATE picks the most recent date in the data model
Thanks for clarification
hi sir,you are using dimdate[date].date every time ,why you are using this .date,since i m getting same result with dimdate[date]
Dimdate[date] will create date table internally. .date to refer a column date. Will do seperate video on this
@@AnalyticswithNags please do video.to clear our dought
Whete is the data set of Dimdate
Search in the Complete playlist
DimDate[Date].[Date] -- why we put .[Date] & why not we put .[Month] or .[Quarter] & What happens we put it.
DimDate[Date] & DimDate[Date].[Date] are different OR Similar ?
In StartDate - LastDate(DimDate[Date].[Date]) in 2016 Qtr 1 January -means it calculating TotalSales from 31-January-2016 ? and what about TotalSales from 01-January-2016 to 30-January-2016 ?
will take seperate video why we need to use .Date
@@AnalyticswithNags I have also same doubt, please tell us what was the reason behind to this.
Hi Sir, Thank you so much for such nice videos. I tried to create a new column using DatesInPeriod but i got an error "A table of multiple values was supplied where a single value was expected". DatesInPeriod is supposed to return a column but why am I not able to create a new column of dates using this function?
Create it as a measure
you can create new table in dates in period.
If my Month count is fix, then You can easily use divide fnc : Divide(Calculate(),3)
Yes
Sir why u used DISTCOUNT in example
To count the distinct values in a column
Why don't we use Firstdate Function
Which ever works you can use. There is not single way to achieve results.
ensure choosing Performance effective way
The Avg measure is not showing in the table, after adding 3 month cum avg in the table then showing as "Can't display the visual"
Not sure what was the issue
Not clear with topics as well as not explining indetail about functions. Suddenly started using variables and don't know why we need to use them. Also dim.date is using form 3rd video but it is almost 10th video and not explained clearly about dim.date.
Dimdate is created using power query. Which will not come understand DAX. Also if it is not available search in the channel any video is available related to that.
ruclips.net/video/BnqtBxGbo0M/видео.html
Moreover, dimdate can be from database, or created in power query or created using DAX.
Soon will create video on DAX
Variable is a temporary storage of any programming language. Which is very basic and self explanatory.
I understand those don't have programming knowledge dont know this. Will soon make video on this.
Prev 3 months cumm average =
var sumtotal = calculate([Total sales], Datesinperiod(DimDate[Date].[Date], LastDate(DimDate[Date].[Date]), -3, Month))
var countmonth = calculate(DistinctCount(DimDate[Date].[Month]), Datesinperiod(DimDate[Date].[Date], LastDate(DimDate[Date].[Date]), -3, Month))
return DIVIDE(sumtotal, countmonth, 0)
Hi thanks for commenting. Share this channel with friends and colleagues. Talk about this channel if you are in LinkedIn