Power BI DAX Tutorial (10/50) - What is DatesInPeriod

[@Littlepeanut9]

Can you explain how the distinctcout(dimdate) function is working here exactly pls?

[@sravani1191]

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

[@AnalyticswithNags]

For each row Filter context Works. Thats why it shows jan 2016

[@adventuresofvyomandkeshav3036]

Same query sir....not understand this row filter context reason. Pls elaborate more.

[@bharatgarg07]

​@@AnalyticswithNags thanks for this explanation.

[@mandrikakumar6519]

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

[@AnalyticswithNags]

Check calculate functions in Dax playlist

[@neelimakagolanu5705]

hi sir, where this average value stores i mean when i consider this return value it not shows in table visual

[@AnalyticswithNags]

All measures(average In this case) are not stored anywhere. They are return on refreshing the reports or filtering some members

[@neelimakagolanu5705]

@@AnalyticswithNags tq so much for reply

[@frustratedgaiii4088]

Hello sir , how you arranged your month names accordingly like Jan, Feb ,march ,mine is not arranged in such manner

[@AnalyticswithNags]

Keep month column order column

[@vishalmane3139]

last 3 months sales excluding the cureent month hw to achieve it

[@poojamaurya5715]

Hi..can u explain how to calculate total.sales except the last 2 day sale

[@AnalyticswithNags]

In calculate use filter and condition like date < today()-2

[@poojamaurya5715]

@@AnalyticswithNags i tried but still facing issue..

[@VishalJaybhaye-kq4hh]

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.

[@AnalyticswithNags]

That’s true. When you use this against any column in chart filter context apply

[@jainavi22]

Hey, Why have we used countmonth variable instead we can simply divide it by 3? I believe it'll give the same input !!

[@AnalyticswithNags]

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

[@kunwarmaurya2025]

@@AnalyticswithNags Thanks for such valuable information. I search similar answer.

[@mayurirajankar2618]

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?

[@AnalyticswithNags]

Explain in detail pls.

[@lalkrishnajha4681]

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

[@AnalyticswithNags]

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

[@AmitSingh-er3lk]

We using DAX function / query under measures....? Or both are different things? and what is M query?

[@AnalyticswithNags]

Watch this you will understand better. kzbin.info/www/bejne/opevcqmLdrefm5Y

[@dasanashiva8792]

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?

[@AnalyticswithNags]

Check previous month calculation video/ dateadd function

[@buzzfeedRED]

why use LAstdate, not able to understand. Can anyone help

[@shwetashetty6455]

LASTDATE picks the most recent date in the data model

[@AnalyticswithNags]

Thanks for clarification

[@sanketthakkar721]

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.

[@AnalyticswithNags]

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

[@siddharthanich]

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)?

[@AnalyticswithNags]

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.

[@relaxbhat]

Whete is the data set of Dimdate

[@AnalyticswithNags]

Search in the Complete playlist

[@venkatareddykummitha5863]

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 ?

[@AnalyticswithNags]

will take seperate video why we need to use .Date

[@Urs_Ajay]

@@AnalyticswithNags I have also same doubt, please tell us what was the reason behind to this.

[@muneeruddinsalauddin5024]

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).

[@AnalyticswithNags]

It depends on the context, can you explain what is your requirement and what you want to do ?

[@UmaMukherjee-y4l]

3:02 for Cumulative moving average you should use AVERAGE instead of SUM😊

[@AnalyticswithNags]

yes sure

[@rohanparakhe4976]

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]

[@AnalyticswithNags]

Dimdate[date] will create date table internally. .date to refer a column date. Will do seperate video on this

[@rohanparakhe4976]

@@AnalyticswithNags please do video.to clear our dought

[@siddharthanich]

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?

[@AnalyticswithNags]

Create it as a measure

[@satyajitbiswal6162]

you can create new table in dates in period.

[@amirtamboli938]

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"

[@AnalyticswithNags]

Not sure what was the issue

[@ahmedmj8729]

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)

[@AnalyticswithNags]

Explore the similar problems here, learn.microsoft.com/en-us/answers/

[@buzzfeedRED]

If my Month count is fix, then You can easily use divide fnc : Divide(Calculate(),3)

[@AnalyticswithNags]

Yes

[@piyushpathak7311]

Sir upload more videos on DAX plz

[@AnalyticswithNags]

Sure in coming weeks

[@manoj4996]

Sir why u used DISTCOUNT in example

[@AnalyticswithNags]

To count the distinct values in a column

[@AnilKumar-ke3yf]

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.

[@AnalyticswithNags]

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. kzbin.info/www/bejne/eJ_UpXWufMeiZq8 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.

[@venkatareddykummitha5863]

Why don't we use Firstdate Function

[@AnalyticswithNags]

Which ever works you can use. There is not single way to achieve results. ensure choosing Performance effective way

[@siddheshdandagavhal9804]

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)

[@AnalyticswithNags]

Hi thanks for commenting. Share this channel with friends and colleagues. Talk about this channel if you are in LinkedIn