Forum Discussion

DanielM16's avatar
DanielM16
Frequent Visitor
5 years ago
Solved

SAMEPERIODLASTYEAR returns Blank

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...
Need Help
  • DanielM16's avatar
    DanielM16
    5 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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.