I am looking to create a field that shows the Current Year Revenue as well as a field to show the Previous Year's revenue on the account object that relates to a revenue field in a custom object. I am wanting to see what the previous year's revenue is and the current year's revenue. Is there a formula using the YEAR function that can calculate that?
Any solution I can think of would require either a custom rollup tool or Flow.
You could create two custom fields on the child object. The first would be a number field to capture the current Calendar Year. You could set the default value on new records to YEAR(TODAY()), but you would need a scheduled Flow to run on the first of each year to update all of the values to the new current year.
The second field would compare that YEAR value to the YEAR value of your custom Date field. That formula would look similar to below.
IF(!ISBLANK(Date__c),
CASE(This_Year__c - YEAR(Date__c),
0, 'This Year',
1, 'Last Year',
'Other Year')
, 'No Date Entered')
You could make the text for values outside of this year and last year whatever you want by modifying what I have as Other Year. You could also evaluate additional years such as -1 for next year, 2 for two years ago, etc...
You can then reference this formula field in the filter conditions of your Roll-Up Summary. Because TODAY()'s date changing does not trigger an update to records, you cannot directly reference TODAY() or NOW() in a formula field used as a Roll-Up Summary filter.
