Author: Rishabh Dubey
Requirement: Create a report that will compare the sales (Closed Won opportunities) of till date of this year vs Same period last year.
(For example, today is 16th March 2022. We want to create a report that will compare the sale of 1st JAN 2022 to 16th March 2022 vs sale of 1st JAN 2021 to 16th March 2021)
Using Standard Salesforce Report filters we will not be able to create a report to meet this requirement. We either need to create a Custom field or row level formula. (we will be creating custom field, but same syntax can be used in Row level formula)
1. Go to setup>Object manager>Opportunity>Fields and relationships
2. Click on new select Formula as data type and check box as return type
3. Name the field – “YTD”
4. Syntax will be as follows
IF(
(CloseDate – DATE(YEAR( CloseDate ), 01, 01))
<=
(TODAY() – DATE(YEAR( TODAY()), 01,01))
, TRUE , FALSE )
5. Give access to the profiles, this field is not required on page layout
6. Go to reports tab,click on new report
7. Select Opportunities as report Type
8. Add below filters
Show Me: All opportunities
Close date: Current and Previous CY
Opportunity Status: Closed Won
Probability: All
YTD: True
9. Group rows by Close date and Add Amount (Sum) to column
10. Click on the Dropdown on the close date column and select the group date by = Calendar Year.
11. Click on Add chart add below configurations
Chart Title: YTD Comparison
X-axis: close date
Y-Axis: Sum of Amount
12. Click on save and Run, The report is ready to share with sales team now.
13. This is how the report will look like
