Loading

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

Дата публикации: Oct 13, 2022
Описание

Relative Date Values are supported for use within standard Salesforce report filters, however filtering based on 'Year to Date' or 'Last Year to Date' require you to add filters and Filter Logic.
 

Решение

 

Option 1)  Use standard report filters

 

1)  Add 2 report filters with the criteria for the current 'Year to Date'
 

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


2)  Add 2 report filters with the criteria for the previous 'Year to Date'
 

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


3)  Combine these filters using Filter Logic:
 

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


 

Option 2)  Create a Custom Field to capture whether 'Date' is within the defined range 


Another way to achieve the requirement is with a new custom field. Here's an example using the 'Opportunities' Object:
 

1. Go to Setup:
 

  • In Salesforce Classic: Select CustomizeOpportunitiesFields
  • In Salesforce Lightning: Select Object Management | Opportunities | Fields & Relationships

2. Under 'Opportunity Custom Fields & Relationships,' click New.
3. For 'Data Type,' select Formula then click Next.
4. Suggested Field Label: Is YTD or LastYTD
5. Select Formula Return Type, then click Next.
6. Click the Advanced Formula tab.
7. Copy and paste the following formula in the Advanced Formula field:
 
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.
9. Enter your desired Field Level Security Settings, then click Next.
10. Select whether you would like to have a checkbox on the Page Layout, then click Save.



See also
Build a Formula Field

Номер статьи базы знаний

000388018

 
Загрузка
Salesforce Help | Article