Forum Discussion

vega's avatar
vega
Resolver III
8 years ago
Solved

Calculated Column Last Year Date

Hello,   I have a Date table and I want to create a calculated column that gives me the date of the previous year. For example, if the date is 1/1/2018 the LY column should be 1/1/2017. Are there a...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    HI vega

     

    Try this Column.

     

    For 29 Feb 2016 it will give 1 Mar 2015

     

    LY Date =
DATE ( YEAR ( DateTable[Date] ) - 1, MONTH ( DateTable[Date] ), DAY ( DateTable[Date] ) )
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi vega,

     Based on my understanding, if this year is the leap year, the previous year column should display blank. For example the previous year of 2/29/2016 day is blank, because there is no 2/29/2015. Please create a calculated column using the formula.

    LY Date_Edit =
IF (
    MONTH ( DateTable[Date] ) = 2
        && DAY ( DateTable[Date] ) = 29,
    BLANK (),
    DATE ( YEAR ( DateTable[Date] ) - 1, MONTH ( DateTable[Date] ), DAY ( DateTable[Date] ) )
)


    Best Reards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

Hi vega,

 Based on my understanding, if this year is the leap year, the previous year column should display blank. For example the previous year of 2/29/2016 day is blank, because there is no 2/29/2015. Please create a calculated column using the formula.

LY Date_Edit =
IF (
    MONTH ( DateTable[Date] ) = 2
        && DAY ( DateTable[Date] ) = 29,
    BLANK (),
    DATE ( YEAR ( DateTable[Date] ) - 1, MONTH ( DateTable[Date] ), DAY ( DateTable[Date] ) )
)


Best Reards,
Angelia