Print this page

Can I have YTD and the same period Last Year on a Dashboard or Report?

Knowledge Article Number 000213470
Description Standard Salesforce filters will allow the use of Relative Date Values, but not Year to Date, or for comparison Last Year to Date

Year to Date, can be filtered on a Report by having two filters:
Date greater or equal to January 1st of this year (a static date)
Date less or equal to Today
Last Year to Date can be filtered on a Report by having two filters:
Date greater or equal to January 1st of last year (a static date)
Date less or equal to 365 days ago
You can combine this with Filter Logic: (1 AND 2) OR (3 AND 4)

Disadvantage, every year one would need to change the static date for first of January.

This Article suggests 2 more ways to achieve this
  1. By using relative date value where user need not change it every year
  2. By using a Formula which can be put in a custom field, it will return True, when the close date is in YTD, or when the Close Date is in the same period Last Year. Now you can filter on this field.
Resolution Using relative date value here are the filters

Year to Date, can be filtered on a Report by having two filters:
  1. Date equal to THIS YEAR
  2. Date less or equal to TODAY
Last Year to Date can be filtered on a Report by having two filters:
  1. Date equal to LAST YEAR
  2. Date less or equal to 365 DAYS AGO
You can combine this with Filter Logic: (1 AND 2) OR (3 AND 4)
Note: For filter 4. you might also use: Date not equal to LAST 365 DAYS

Also users might not set the dates correctly if they try to remove it hence below is another way to achieve the requirement but using custom field


Steps to create a custom field on Opportunities:
  1. Setup - Customize - Opportunities - Fields
  2. Scroll down to Opportunity Custom Fields & Relationships, click on New
  3. Data Type: Formula, Next
  4. Suggested Field Label: Is YTD or LastYTD
  5. Formula Return Type: Checkbox, click Next
  6. If necessary change to the Advanced Formula tab
  7. Copy and paste the following formula into the box, and click Next
    1. 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. Choose the Field Level Security Settings, click Next
  9. Choose whether you would like to have the check-box on the Page Layout, click Save

Step for a simple example Report with a Chart:
  1. Go to the Reports tab
  2. Click on new Report...
  3. Open the Opportunities folder and choose the Opportunities Report Type, click Create
  4. In the left column search for the new field, pull it onto the Filter area, choose Equals, value: True
  5. In the Filter area, choose Date Field Close Date, Range: Current and Previous CY
  6. Hover over the column name for the Amount field, click the down arrow, choose Summarize this Field..., choose Sum
  7. Hover over the column name for the Closed Date field, click the down arrow, choose Group by this Field
  8. For the Grouping, click on the arrow, choose Group Dates By: Calendar Year
  9. Click on the Add Chart button, click OK
  10. Save your Report




promote demote