Difficulty with SAMEPERIODLASTYEAR

[a68tbird]

Hello All -

  Having some problem wrapping my head around the SAMEPERIODLASTYEAR function.  After having much difficulty actually trying to get anything but a blank value, this article helped me very much: same period last year using SAMEPERIODLASTYEAR return nothing.  Surrogate keys in the date dimension table do not work with the SAMEPERIODLASTYEAR function. 

However, what I am getting returned now is the entire year of sales, and that's not what I was expecting.  The measure that I have is:

Sales LY = CALCULATE([Sales YTD],SAMEPERIODLASTYEAR(CalendarTable[Date]))

where the [Sales YTD] measure is:

Sales YTD = TOTALYTD(SUM(OrderItem[PriceExclTax]),CalendarTable[Date])

I assumed that SAMEPERIODLASTYEAR would filter on the [Sales YTD] measure, but it's not. Should I be including a further filter on my [Sales LY] measure?

Thanks in advance for the assistance.

[kcantor · Accepted solution]

@a68tbird

ARe you putting in the months on the axis so that the formula has a way to break down the measure? It is doing exactly what the DAX say. It is calculating year to date sales. When you add same period last year, your measure still requests the year to date total. You would need to specify start and end dates within your same period measure to create a difinitive cut off for totals.

[a68tbird]

Bah! Of course! I was simply comparing values on card visuals, but once I plot them on a line chart, it works. 

Thanks!

[KHorseman]

@a68tbird you could potentially force it to work on a card by adding a visual level filter to the card that gives it a date context. My standard date table for instance has a column called YearDiff and a column called DayDiff. If I added two visual level filters to a card, DateTable[YearDiff] = 0 and DateTable[DayDiff] <= 0, that would make the card always start with a date context of all the days between January 1 of the current year until today.

To recreate those columns in your own table:

DateDiff = INT(DateTable[Date] - TODAY())

YearDiff = INT(YEAR(DateTable[Date]) - YEAR(TODAY()))

The past is negative numbers, the present is 0, and the future is positive. I leave MonthDiff as an exercise to the reader.