Forum Discussion
Previous Year calculation based on date slicer -- multiple date / discontinuous date selected
- The error happens because SAMEPERIODLASTYEAR (and DATEADD when used as a filter modifier) requires a contiguous, single-year date selection. The moment your slicer has gaps or crosses year boundaries, that contract is broken and the function refuses to evaluate.
The usual fix is to push the year shift into a row-by-row iteration so each selected date is shifted on its own:
TotalSalesPY =
SUMX (
VALUES ( 'Date'[Date] ),
CALCULATE ( [TotalSalesCY], DATEADD ( 'Date'[Date], -1, YEAR ) )
)
Because each iteration only sees one date, DATEADD is always contiguous and the result aggregates cleanly across single dates, multi-date same-year, gaps, and spans across years. Make sure your Date table is marked as a Date Table and the relationship to Sales is on 'Date'[Date].
If this worked for you, kindly mark it as the solution and give a thumbs up.
Best,
Shai Karmani
- The error happens because SAMEPERIODLASTYEAR (and DATEADD when used as a filter modifier) requires a contiguous, single-year date selection. The moment your slicer has gaps or crosses year boundaries, that contract is broken and the function refuses to evaluate.
The usual fix is to push the year shift into a row-by-row iteration so each selected date is shifted on its own:
TotalSalesPY =
SUMX (
VALUES ( 'Date'[Date] ),
CALCULATE ( [TotalSalesCY], DATEADD ( 'Date'[Date], -1, YEAR ) )
)
Because each iteration only sees one date, DATEADD is always contiguous and the result aggregates cleanly across single dates, multi-date same-year, gaps, and spans across years. Make sure your Date table is marked as a Date Table and the relationship to Sales is on 'Date'[Date].
If this worked for you, kindly mark it as the solution and give a thumbs up.
Best,
Shai Karmani
Thank you, Shai ! This worked and I learnt a lot from the explanation !