If you are a SFDC Admin and wondered about how to create a Year to Date or Month to Date comparison report using Salesforce, this post offers you the solution from the great Steve MO!! For folks who do not know Steve MO, he is an MVP and WHIZ who has answered more than 10,000 questions in the success community on reports. He had a session with our Philly Salesforce User Group and I had the honor of listening to his session. Based on his session, I am sharing 2 reports which would be a great value add for you to WOW your users!!
Year to Date and Month to Date Opportunity Comparison report
If you use opportunities and want to show to your sales team how the opportunity pipeline was year after year or month over month, it is a challenge.
Solution:
- Create 2 formula fields on your opportunities of type check box on your opportunities Year-to-Month and Year-to-date.
- Custom Field Datatype = Formula Result = Checkbox
Formula: Year-to-MonthMONTH(CloseDate) <= MONTH(TODAY())
Month-to-date = MONTH(CloseDate) <= MONTH(TODAY())
- Formula: Year-to-Date
(CloseDate – DATE(YEAR( CloseDate ), 01, 01) ) + 1
<=
(TODAY() – DATE(YEAR( TODAY() ), 01, 01) ) + 1
Year to date formula field = (CloseDate - DATE(YEAR( CloseDate ), 01, 01) ) + 1 <= (TODAY() - DATE(YEAR( TODAY() ), 01, 01) ) + 1
4. Once you create the 2 formula fields in opportunity, you can create an opportunity report with the following filters
a. Close Date = Current and Previous 2 Fiscal Years.
b. Year-to-Date checkbox equals true.
This would create the following report which you can see on the below screenshot.
If you run the same report with Month to Date checkbox equals true, you would get a report of all opportunities based on the current month, which is cool.
This helps your sales team understand trends in your pipeline and how you are progressing on your current pipeline compared to past history. Use data to monitor trends compared to guessing. I would like to thank Steve MO for sharing the deck and the report. Please fill the below form which would subscribe you to my blog and you would receive the link to download SteveMo presentation in your email. Feel free to post your comments and email me at buyan@eigenx.com for further questions.