Forum Discussion
Previous Year calculation based on date slicer -- multiple date / discontinuous date selected
Hi,
I have a project on comparing the sales data in current year and previous year, and here are my DAX codes for the measures:
TotalSalesCY = SUM (Sales[Amount])
TotalSalesPY = CALCULATE ( TotalSalesCY, SAMEPERIODLASTYEAR(Date[Date]))
I have a slicer using the date table date column named "Date[Date]", and i set it to "select single item" only, it works well.
howevern, my user would like to have a date slicer with more functions:
1. select multiple dates;
2. select discontinous dates, e.g. Jan 2026 and Apri 2026, don't select Feb and Mar;
3. the selected dates could be in different years, e.g. Dec 2025 , Feb 2026, without Jan 2026.
My current Dax solution worked on multiple date selected when they are in the same year, but on condition 3, when 2 or more dates in differernt year were selected, my CY and PY sales measure both failed with the error msg below:
And also not work on "DATEADD( DATE[Date], -1, year), same reason.
Then I tried to grab the dates one year before selected by
but on Returning this / use it in a calcalate func it also failed.
If the dates selected are not contiguous , the daterange by min(date) to max(date) also won't work.
I am looking for other solutions on this, and also wondering why my CY sales also failed on it.
Thank you!
- 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
2 Replies
- Shai_Karmani
Super User
- 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
- MiaSunny
Helper I
Thank you, Shai ! This worked and I learnt a lot from the explanation !