Forum Discussion
SAMEPERIODLASTYEAR and Week Number Groupings/Calculations Not Returning Expected Result
I am currently trying to set up a bar and line chart showing CY and PY sales data, grouped into week numbers on the X axis. The issue is that the SAMEPERIODLASTYEAR function is returning the incorrect week number for what I want to be shown - for example, the week one 2025 data point uses a date of 1/5/2025, and when the SAMEPERIODLASTYEAR function looks up this date in the prior year, it returns a date grouped into week 2, but there is a data point in the data set using date 1/7/2024, which is the last day of the first week of 2024 - shouldnt this be labeled as week 1, not week 2? All dates in this data set are based on the last day of the week. Essentially all weeks returned by SAMEPERIODLASTYEAR are one week later than they should be - week one is returning as week 2, week 2 is returning as week 3, and so on.
-I am using the WEEKNUM function with option 2, as the data set also uses Monday as the start of the week
-I have a properly marked date table and am using the calendar min max setup based on fact table dates
Hi bradbel ,
Try below:
PY Sales (Week Match) = VAR CurrentWeekNum = SELECTEDVALUE('Date'[WeekNum]) VAR CurrentYear = SELECTEDVALUE('Date'[Year]) RETURN CALCULATE( [Total Sales], ALL('Date'), -- Remove existing filters 'Date'[Year] = CurrentYear - 1, 'Date'[WeekNum] = CurrentWeekNum -- Forces Week 1 to match Week 1, ignoring 53rd weeks )this forces you to go to last year and apply the same weekfilter.
Please give kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
4 Replies
Hi bradbel ,
Try below:
PY Sales (Week Match) = VAR CurrentWeekNum = SELECTEDVALUE('Date'[WeekNum]) VAR CurrentYear = SELECTEDVALUE('Date'[Year]) RETURN CALCULATE( [Total Sales], ALL('Date'), -- Remove existing filters 'Date'[Year] = CurrentYear - 1, 'Date'[WeekNum] = CurrentWeekNum -- Forces Week 1 to match Week 1, ignoring 53rd weeks )this forces you to go to last year and apply the same weekfilter.
Please give kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
This is incredible - solution verified, data now aligns exactly how I wanted it to. Thank you so much for the quick response!
bradbel There really isn't much you can do about how SAMEPERIODLASTYEAR works. Instead, you will have to calculate the desired date range manually using the FILTER and WEEKNUM functions. Sunday the 7th of January is week 2 because SAMEPERIODLASTYEAR is using standard calendar defintions where the week starts on Sunday and ends on Saturday. That is also the default way that WEEKNUM works which you are modifying that behavior by using 2 as the second parameter.
weeks and years are incompatible. One alternative is to use DATEADD(..,-364,DAY)
