Forum Discussion
Why does Quantity LY not work?
- 6 years ago
OK. Here's the full and ultimate explanation of what's going on. It's enough to check what this
FILTER ( Dates, SAMEPERIODLASTYEAR ( Dates[Date] ) )does. FILTER is an iterator, so it takes what's currently visible in Dates and goes through this row by row. What does SAMEPERIODLASTYER do? Let's see what dax.guide/sameperiodlastyear/ says:
Remarks
The dates argument in SAMEPERIODLASTYEAR can be any of the following:
- A reference to a date/time column. Only in this case a context transition applies because the <Dates> column reference is replaced by
- CALCULATETABLE ( DISTINCT ( <Dates> ) )
- A table expression that returns a single column of date/time values.
- A Boolean expression that defines a single-column table of date/time values.
In other words, what happens under FILTER is this:
FILTER ( Dates, SAMEPERIODLASTYEAR ( // Here is where context transition // happens... CALCULATETABLE ( DISTINCT ( Dates[Date] ) ) )So what is it that SAMEPERIODLASTYEAR sees? Well, it sees the date that belongs to the row that is currently being iterated. Takes this value and moves it back 1 year. Now is the fun part. If it so happens that the shifted date is not BLANK, the row will be included, if it is BLANK (because you've fallen off the edge of the calendar), the row will be filtered out. So, what you get as a result depends on which chunk of dates you see but the semantics of the code is this: From the currently visible dates return only those that do not hit BLANK when shifted back one year.
This is the full and ultimate explanation of what you see.
- A reference to a date/time column. Only in this case a context transition applies because the <Dates> column reference is replaced by
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, and you are 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 dates 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.