Filter a Report for Relative 'Year to Date' Date Ranges

This article outlines two approaches to filter a Salesforce Report for Year to Date (YTD) and Last Year to Date date ranges.

Option 1: Use Standard Report Filters

Step 1: Add 2 report filters for the current Year to Date

• Date equal to THIS YEAR
• Date less or equal to TODAY

Step 2: Add 2 report filters for the previous Year to Date

• Date equal to LAST YEAR
• Date less or equal to 365 DAYS AGO

Step 3: Combine these filters using Filter Logic

• Filter Logic: (1 AND 2) OR (3 AND 4)

Option 2: Create a Custom Formula Field

Another approach is to create a custom formula field on the Opportunities object that identifies whether a record's Close Date falls within the current or previous Year to Date range.
The formula evaluates two conditions:

1. Whether the CloseDate falls between January 1 of the current year and today — representing Year to Date.
2. Whether the CloseDate falls between January 1 of the previous year and the same calendar date last year — representing Last Year to Date.

The formula accounts for leap years by defaulting to February 28 when the current date is February 29.
Steps to create the custom field:

1. Go to Setup:
   1. In Lightning Experience: Select Object Manager > Opportunities > Fields & Relationships
   2. In Salesforce Classic: Select Customize > Opportunities > Fields
2. Under Opportunity Custom Fields & Relationships, click New
3. For Data Type, select Formula, then click Next
4. Enter Field Label: Is YTD or LastYTD
5. Select Formula Return Type: Checkbox, then click Next
6. Click the Advanced Formula tab
7. Enter the formula logic as described above (checking current YTD and last YTD conditions, with leap year handling)

   OR (
        /* Check whether close date is YTD */
        AND (
            DATE ( YEAR ( TODAY() ), 1, 1 ) <=  CloseDate,
            CloseDate <= TODAY()
            ),
        /* Check whether close date is YTD-1 */
        AND (
            DATE ( YEAR ( TODAY() )-1, 1, 1 ) <=  CloseDate,
            CloseDate <=   IF (
               /* Calculate TODAY()-1 YEAR, prevent trying to set last year to Feb 29th */
               AND (
                   MONTH(  TODAY() ) = 2,
                   DAY( TODAY() ) = 29
                   ),
               DATE( YEAR( TODAY() ) - 1, 2, 28),
               DATE( YEAR( TODAY() ) - 1, MONTH( TODAY() ), DAY( TODAY() ) )
               )
            )
      )

8. Click Next, configure Field Level Security, then click Save