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())
Month-to-date =
MONTH(CloseDate) <= MONTH(TODAY())
  1. 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.

[optin-cat id=”1178″]

Please subscribe

Subscribe to our mailing list and get tips to maximize salesforce to your email inbox.

Thank you for subscribing.

Something went wrong.

Share:
buyan47

Author: buyan47

Hi there! My name is Buyan Thyagarajan. I am a Salesforce consultant specializing in Higher Education, Manufacturing and Marketing Automation. My blogs will help you to maximize your Salesforce CRM investments, prevent problems beforehand and make the right decisions. If you need to talk to me right away, you can email me at buyan47@gmail.com or call me at 302-438-4097

5 thoughts on “Steve Mo- Dynamic Year to date and Month to date opportunity comparison report

  1. Hello Buyan,
    I tried the solution described above, as I thought this reporting would be useful for my org, and I got stuck once the report was built. I can’t tell whether it is not working as expected or if I am just missing something about how to set up the chart. Can you go into more detail about how you got from report criteria to the chart you posted. Also, I am not sure the formulas are working in my instance. I should only be seeing close dates in the current month, and they are all over the place. Would you be able to help me trouble shoot a bit?

    1. Hi Ashlynn,
      Sorry that the report is not working for you. Once you create the 2 formula fields on the opportunities, the screenshot is I show is a lightning report. So I am not sure whether you use classic or lightning? You create a report on opportunities which is a matrix report. In the matrix report, you create groupings by close date and close month and group by any one of the 2 checkbox fields. If you still struggle with it, please email me to buyan@eigenx.com with a screenshot on your end on the formulas you created, type of report you created and I will help you further?
      Buyan

  2. Buyan,
    I am trying to create the 2 formula fields, I created the first one with no issue.
    But the second one, Year to Date I am getting an syntax error.
    I put this into the box:

    (CloseDate – DATE(YEAR( CloseDate ), 01, 01) ) + 1
    <=
    (TODAY() – DATE(YEAR( TODAY() ), 01, 01) ) + 1

    Should it be:
    Year to Date (CloseDate – DATE(YEAR( CloseDate ), 01, 01) ) + 1
    <=
    (TODAY() – DATE(YEAR( TODAY() ), 01, 01) ) + 1

    1. Hi Andrea,
      Sorry for the delayed response on this. I will look into this and discuss this with you on tomorrow call.
      Buyan

Leave a Reply

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