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 - I am not sure who gave you the advice that you must always explicitely use FILTER in a CALCULATE but that is bad advice. You do not and there is actually guidance that you should avoid doing so.
https://docs.microsoft.com/en-us/power-bi/guidance/dax-avoid-avoid-filter-as-filter-argument
But then again, I tend to avoid using CALCULATE.