Calculating Total Sales LY (specify FILTER() )

Hello there,

I’m working through this video 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])))

Many thanks,

Hi @michellepace

In DAX only Calculate and Calculatetable functions has ability to Add additional context or Change the context.

Second logic SAMEPERIODLASTYEAR is inside Filter…Filter doesn’t change the Context

Hope this clarifies.

Hi @michellepace,

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"

… is that correct?

Nope I am saying that you need to understand how a function is evaluated, what it returns and when to use it.

Hi @michellepace

Yes Time Intelligence functions which returns a table… we’ve to use directly inside calculate function

Only Calculate function can change the Context.

We can achieve same result using filter function also, but we’ve to write our own code.,year-to-date%20calculation.

Hope this clarifies

Thank @Rajesh, that does help with my understanding for sure. Thank you.


Welc :smile:

Let me know if you need any further help.