How to Create a Calculate Column for The Same Day of Last Year

Posted on by Summa Lai

You can create a calculated column in Calendar Dimension:

SameDayLY= 
var TodayDate = TODAY()
var LastYear = YEAR(TodayDate)-1
var LastMonth = MONTH(TodayDate)
var LastDay = DAY(TodayDate)
return
'Calendar'[Date]= DATE(LastYear,LastMonth,LastDay)

This solution shoule be good for most of the situation.

However, your way returned a true value for "2019-03-01" if Today date was a Leap day for example "2020-02-29" but that was a wrong behavior according to some business requirements so you could handle this as follow:

SameDayLY =
var TodayDate = TODAY() // "2020-02-29"
var TodayYear = YEAR(TodayDate)
var LastYear = YEAR(TodayDate)-1
var LastMonth = MONTH(TodayDate)
var LastDay = DAY(TodayDate)
return
IF ( MOD(TodayYear,4) = 0 && LastMonth = 2 && LastDay =29 , FALSE() , 'Calendar'[Date]= DATE(LastYear,LastMonth,LastDay))