Microsoft Excel DAX - Sameperiodlastyear returns blank

Question

Hi, I have a problem where I want to extract the sales of an item but from last year, I saw a lot of people with similar problems where they had a year working but that's it, that's not my case, I get all of them as blanks, and I can't seem to find a solution, I have the calendar table set as a date table with a relationship to the date column in the sales table, but whenever I have =Calculate([Total sales], SAMEPERIODLASTYEAR('Calendar'[Date]) it's blank, I even tried =Calculate([Total sales], SAMEPERIODLASTYEAR('Calendar'[Date],ALL('Calendar'[Date]) but still nothing. I cant seem to fix it.

Please help

Answer 1

Hi Carlos,

Your =Calculate([Total sales], SAMEPERIODLASTYEAR('Calendar'[Date]) formula should work.

1. Check the data to ensure there is sales in those past periods.
2. Check the context of the PIVOT table where you are using you measure.
3. Check the relationship is setup correctly.

I use the same formula in the snapshot below and you can see the "Prior" sales highlighted in yellow. Note that the prior sales in Jan, Feb, Mar of 2023 in my Pivot are blank because there is no data for those periods.