SAMEPERIODLASTYEAR returns Blank

[DanielM16]

I have data that looks like this:

Cut-off Date	Aux_Date	Category	Sales
23.01.2018	01.01.2018	A	1200
24.01.2019	01.01.2019	A	8900
25.01.2020	01.01.2020	A	6400
23.01.2018	01.01.2018	B	1100
24.01.2019	01.01.2019	B	5900
25.01.2020	01.01.2020	B	2100
23.01.2018	01.01.2018	C	4800
24.01.2019	01.01.2019	C	2600
25.01.2020	01.01.2020	C	1300

For a selected Cut-off Date, I want to show the sum of sales for both the selected date and the date one year prior. My issue now is that I can't just use SAMEPERIODLASTYEAR on the Cut-off Date, since the exact day changes from year to year (in 2018, it's the 23rd; in 2019, it's the 24th). I figured I could just add an auxiliary column that contains the first of each month rather than the actual cut-off date (see column Aux_Date). I built two simple measures:

Sum_Sales = SUM(Data[Sales])

and

Sum_Sales_LY = CALCULATE([Sum_Sales], SAMEPERIODLASTYEAR(Data[Aux_Date])).

But when I put these on my dashboard, Sum_Sales_LY will show (Blank), if I filter on Cut-off Date:

It works fine when I filter on Aux_Date, but that's not what I want:

Is there any way to utilize SAMEPERIODLASTYEAR without having to switch to Aux_Date as filter?

Maybe I'm struggling because I can't quite wrap my head around as to why SAMEPERIODLASTYEAR is a valid input for the <filter> argument of the CALCULATE formula. The documentation suggests that SAMERPERIODLASTYEAR returns a single column of data (i.e. the dates incremented by -1 year), and I don't understand how that serves as a filter to anything.

[DanielM16 · Accepted solution]

I actually found the solution myself. Changing the measure to the following does the trick:

Sum_Sales_LY =

var ly = SAMEPERIODLASTYEAR(Data[Aux_Date])

return CALCULATE([Sum_Sales], all(Data[Cut-off Date]), Data[Aux_Date]=ly)

[amitchandak]

@DanielM16 , Please use the date table in all such cases

Year behind Sales = CALCULATE([Sum_Sales],dateadd('Date'[Date],-1,Year))

Year behind Sales = CALCULATE([Sum_Sales],SAMEPERIODLASTYEAR('Date'[Date]))

Refer to my video why TI fails: https://www.youtube.com/watch?v=OBf0rjpp5Hw

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.