SAMEPERIODLASTYEAR and Week Number Groupings/Calculations Not Returning Expected Result

[bradbel]

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

[lbendlin]

weeks and years are incompatible. One alternative is to use DATEADD(..,-364,DAY)

[GeraldGEmerick]

@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.