Hi community,
I’m doing the course ’ Mastering DAX Calculations’ and hopefully repeating it step by step in my work file. However I got stuck with the very basic formula and can’t get it to work.
So the formula is:
Date LY = DATEADD( Dates[Date], -1, YEAR )
The visual crushes with an error
‘a table of multiple values was supplied where a single value was expected’.
The Dates table is built with the function designed by Enterprise DNA.
What is the trick that I’m missing here?
Hello @StaceyL,
Thank You for posting your query onto the Forum.
“DATEADD()” is a table function and when used in a measure it needs to be accompanied by an expression. In a measure, you cannot use the table function nakedly since measure returns the scalar value and not the values in the tabular format and therefore, you’re getting the error.
It should be something like this in a measure -
Sales LY =
CALCULATE( [Total Sales] ,
DATEADD( Dates[Dates] , -1 , YEAR ) )
Below are the links of the article as well as videos provided pertaining to this topic. Go through them again for the reference.
Hoping you find this useful and helps you in understanding the topic. 
Thanks and Warm Regards,
Harsh
Hi @Harsh , thank you for stepping in. I tried to troubleshoot hence cut the formula. Originally it was exactly the same like you in your example. Can’t grasp why it went wrong.
Hello @StaceyL,
The reason why it’s incorrect is because it’s returning a values in the form of “TABLE”. Measures can only return the “SINGULAR/SCALAR” values and not the entire table. When you write a measure like that it creates a table. Below is the screenshot provided for the reference -
If you want the results in the form of table then go to the Modelling section and put the same measure inside a table and you’ll see the difference.
Otherwise, as I said in my previous post, you’ll have to use the expression i.e., a “Measure” alongwith the table function in order to convert the results into a one single value rather than returning the table as a result.
Thanks and Warm Regards,
Harsh
Thank you, @Harsh. The initial formula was exactly the one you suggested, i.e. DateAdd was ‘wrapped’ in Calculate function. Yet it didn’t work either. Please see the screenshot below.
The [Total Sales] measure is SUM( Sales[Total Revenue] ). The Dates and Sales tables are linked.
Sure thing, please find attached.
Mastering+DAX+Calculations±+My+File_.pbix (636.7 KB)
Hello @StaceyL,
Thank You for providing the working PBIX file.
Everything’s working fine. The results are also getting corerctly evaluated. The reason why you were getting the results as “Blanks” is because your data starts from Aug 2019 so in order to evaluate the results for the previous year you need to expand your date range into the slicer selection.
The range that you had selected into the slicer was less than Aug 2020 and therefore it didn’t evaluated the results for the previous year and provided it as “Blanks”.
Below is the screenshot of the final results provided for the reference -
I’m also attaching the same PBIX file for reference with expanded slicer selection.
Hoping you find this useful and meets your requirements that you’ve been looking for.
Thanks and Warm Regards,
Harsh
Mastering+DAX+Calculations±+My+File_ - Harsh.pbix (592.1 KB)
Thank you for the detailed answer @Harsh 
It’s great to know that you are making progress with your query @StaceyL.
Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.
We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,We hope you’ll give your insights on how we can further improve the Support forum. Thanks!
Hi @StaceyL, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.
Thank you @Harsh . Like you suggested it looks the issue was related to the dates range.
