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
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
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
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?
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
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
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 ?
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.
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.
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?
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?
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. th-cam.com/video/BnqtBxGbo0M/w-d-xo.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.
Can you explain how the distinctcout(dimdate) function is working here exactly pls?
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
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.
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/
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 ?
3:02 for Cumulative moving average you should use AVERAGE instead of SUM😊
yes sure
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
We using DAX function / query under measures....?
Or both are different things?
and what is M query?
Watch this you will understand better. th-cam.com/video/lfLAvUARle4/w-d-xo.html
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
last 3 months sales excluding the cureent month hw to achieve it
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 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
Sir upload more videos on DAX plz
Sure in coming weeks
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.
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
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, 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,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
Sir why u used DISTCOUNT in example
To count the distinct values in a column
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
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
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.
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.
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.
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.
th-cam.com/video/BnqtBxGbo0M/w-d-xo.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.
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