Get Current and Prior Year Dates from Data

[MLRM]

I am trying to use the Data that I have currently and transform the Fiscal End Date Column into two additional columns where one has the current Year of 2017 and another column that has the prior year of 2016. For example I need Dates for Row 1 to look like 2017-04-30 and 2016-04-30. Is there a simple way / formula to get these new years added to the dates. The Month and Days of the date are good I just want to change the year. Thanks so much. 

 

[Joe_Mako · Accepted solution]

Would these formula expressions be what you are looking for, they are relative to the current system clock date:

Current:

DateTimeAdd([Fiscal End Date(1162)],
ToNumber(DateTimeFormat(DateTimeToday(),"%Y"))-
ToNumber(DateTimeFormat([Fiscal End Date(1162)],"%Y"))
,"year")

Prior:

DateTimeAdd([Fiscal End Date(1162)],
ToNumber(DateTimeFormat(DateTimeToday(),"%Y"))-
ToNumber(DateTimeFormat([Fiscal End Date(1162)],"%Y"))

-1
,"year")

[MLRM]

Yeah that works perfect. I just had to change the Formulas to DateTimeAdd but other than that it works great. So to double check these formulas are now dynamic in that when it becomes 2018 the current year and Prior Year will also change accordingly then. Thanks so much for the help. 

[Joe_Mako]

Yes it is relative to the current date, so on Jan 1st 2018, the current year for all will be 2018. Thanks for the note on the wrong formula, I fixed my typo :)

[JessicaS]

Hello,

This post has been moved to 'Data Preparation and Blending' for greater visibility.

Thanks!