Steve Mo- Dynamic Year to date and Month to date opportunity comparison report

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:

  1. Create 2 formula fields on your opportunities of type check box  on your opportunities Year-to-Month and Year-to-date.
  2. Custom Field Datatype = Formula Result = Checkbox
    Formula: Year-to-MonthMONTH(CloseDate) <= MONTH(TODAY())

 

  1. Formula: Year-to-Date
    (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.

Please subscribe to my blog to get Sfdc tips on a monthly basis.
Enjoy Steve Mo reporting and formulas in your welcome email!!
We respect your privacy.

 

 

 

4 Comments

  1. ashlynn sylvain July 20, 2018
    • buyan47 July 21, 2018
  2. Andrea July 24, 2018
    • buyan47 August 5, 2018

Leave a Reply to ashlynn sylvain Cancel reply

Your email address will not be published. Required fields are marked *

Get free tips on Salesforce
Get free tips on Salesforce
We respect your privacy.