Microsoft Excel DAX - Sameperiodlastyear returns blank

Carlos Escutia 0 Reputation points
2023-11-02T06:40:12.7966667+00:00

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

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,620 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Andrew Wathana Hosakul 0 Reputation points
    2024-05-10T15:44:42.31+00:00

    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. User's image

    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.

    User's image

    0 comments No comments