Hello @ysherriff,
Thank You for posting your query onto the Forum.
Well, there’re several reasons about why you’re not getting the results as expected. Let’s see those reasons one-by-one as listed below -
1). Inside “DATEADD()” function, you’ve put the condition as “previous one year” in order to retrieve the results. So in-directly inside the measure, you’re saying that retrieve all the results of the same period last year. Since the logic inside the measure is in the form of “Same Period Last Year”, it’s bound to give the results as blanks.
The reason being is, the relationship has been established between the Fact table and Date table based on “Created Date” field from the Contacts table. And if you check the data for the year 2021, the first data point has the date from “4th Oct, 2021” (Below is the screenshot provided). So since there’s no data for the month of “Aug, 2021”, it’s giving the results as blanks.
2). The alternative measure which you’ve created provides the results based on the entire previous year and not based on same period last year.
What “DATEADD()” function does is, it provides the results based on “same day previous period” and that previous period could be anything i.e., either day, month, quarter or year. So for example, if we’re on 17th Aug, 2022 and want to see the results in comparison to last year same day i.e., for 17th Aug, 2021 then in that case, “DATEADD()” is to be used.
3). In the case of Time Intelligence analysis, especially when Time Intelligence related DAX functions are used (“DATEADD()” function is also one of them). Remember to provide the date context either internally i.e., inside the visual itself or externally i.e., in the form of slicer.
What does that mean? It means that, when you perform these kinds of analysis using time intelligence functions and if there’s no date context provided then also visuals tend to go blank. So in those type of scenario’s, either bring any date field from the date table (based on business scenario/requirements) inside the visual itself or create a slicer so that correct values are evaluated in correspondence with the date context.
4). Lastly, based on the above mentioned points, the same results which you’ve showcased for an entire year 2021, can be achieved by using the time intelligence function as well i.e., by using “PREVIOUSYEAR()” DAX function. Below is the measure provided for the reference -
Leads PY - 2 =
CALCULATE( [Total Prospects] ,
PREVIOUSYEAR( 'Date'[Date] , "31/12" ) )
As stated in Point No. 3, when we make use of time intelligence functions, we need to provide date context. And therefore, in this case, I’m adding a year slicer onto the page and then only it’ll showcase the exact same results otherwise it’ll give the results as blanks. Below is the screenshot provided for the reference -
I’m also attaching the working of the PBIX file for the reference purposes.
Hoping you find this explanation useful and clears your understanding about - “Why “DATEADD()” function is not yielding the expected results?”.
Thanks and Warm Regards,
Harsh
PPD Prospects Dashboard - Harsh.pbix (19.3 MB)
