I’m working through this video https://portal.enterprisedna.co/courses/195681/lectures/2989715 of Sam’s on the calculate function. He refers to Total Sales but for the example below, I’ll use Total Quantity. Could somebody please tell me why the second DAX expression yields incorrect results? I thought for best practices purposes I’ll explicitly state the Filter function… but the calculated answer is wrong.
Total Quantity LY (right) = CALCULATE([Total Quantity], SAMEPERIODLASTYEAR(Dates[Date]))
Total Quantity LY (wrong) = CALCULATE([Total Quantity], Filter(Dates, SAMEPERIODLASTYEAR(Dates[Date])))
Thake a look at individual functions: FILTER and SAMEPERIODLASTYEAR. Both of them will return a “reduced set of a table”, and in this case you could consider SAMEPERIODLASTYEAR a “special filter function” for Dates table, that’s why you should not include it in the FILTER function, but eventually you could include it as a second filter expression for CALCULATE.
@michellepace SAMPERIODLASTYEAR is a table function and you are using it in the second argument of FILTER where boolean conditions are evaluated to return the list of values from the first argument of FILTER that satisfy the criteria specified in the second argument.
Both FILTER and SAMPERIODLASTYEAR are doing their job and both are getting evaluated in the filter context, how to verify that SPLY is evaluated in the filter context? SAMPERIODLASTYEAR ( Dates[Date] ) is equivalent to saying SAMPERIODLASTYEAR ( VALUES ( Dates[Date] ) )
But the presence of SPLY has no impact on the FILTER ( Dates ) part, you must be getting the result similar to the current year.
Let’s say filter context is 2020. refer to the comments in the below image:
But when you use just SAMPERIODLASTYEAR, it takes the current date and shifts back one year and gets the dates of 2019 and applies it to the Filter context and hence you get the correct result.
Hello @AntrikshSharma and @Lucian . Thank you both for your replies. I don’t quite grasp the intricacies of the explanation but will check back in a few weeks once I have learnt a bit more.
But to summarise, are you both basically saying to me,
"never use a time intelligence function within FILTER( ) because that’s duplicating the filtering and you’ll get the wrong answer"
