+44 203 280 3665

Anyone that runs a sales team needs to be able to quickly identify dud pipeline deals. Those opportunities that have little chance of ever closing successfully but which are inflating the size of the pipeline. The ones that give you a false sense of future sales revenue.

The dashboard table on the left gives you this information. It ranks the 10 Opportunities on which the Close Date has been changed the most number of times. And shows the Created Date for each Opportunity.

Look at the Opportunity at the top of the table for example. It was created in June 2013. That’s 9 months ago. And the Close Date has been changed 13 times. If your typical sales cycle is (say) 3 to 4 months, is that a solid pipeline deal that you can rely on? Or is it consistently being moved along in order to maintain the size of the sales pipeline?

Either way, this powerful table gives busy Executives the information they need to drill into specific areas of the sales pipeline in more detail.

The detailed instructions on how to configure Salesforce to create the underlying report and dashboard table are contained in the rest of this blog. If it’s a management perspective you’re after then review our list of other blog posts on the effective use of Salesforce dashboards.

Like all Salesforce dashboard charts and tables this one is based on an underlying report. Here’s the report that drives this table. It’s a matrix report that lists all open Opportunities by Opportunity Owner and displays the Created Date across the top.

salesforce report showing the number of Opportunity Close Date changes

There’s one figure within the report for each Opportunity. That’s the number of times the Close Date has changed. We’ve added some conditional highlighting to make it easier for the eye to pick out the higher numbers.

Create the metric that counts Close Date changes

The field that’s driving the report is on the Opportunity. It’s the “# times Close Date has changed”.

Opportunity field that automatically counts the number of Close Date changes

Here’s how to make the field update automatically every time the Close Date is changed.

First create your field. The Field Type needs to be Number. The essential thing about this field is to set the Default Value to zero.

Workflow rule that updates the metric that counts the number of times the close date has changed

Then you need to create a Workflow Rule. Here are the three key things to know about this workflow rule.

  • Set the Evaluation Criteria to “Created, and every time it’s edited”. We need this option because we want the workflow rule to be evaluated every time the Opportunity is updated.
  • Select the “Formula evaluates to true” as the Rule Criteria.
  • Use the formula ISCHANGED (Close Date) as the formula entry. In other words, the workflow rule should fire every time the Close Date is changed.

Create a workflow rule with ISCHANGED (CLOSE DATE) evaluation criteria

Then create a Field Update that is triggered whenever the workflow rule is fired.

Field update that is triggered when the workflow rule is fired and increments the field that counts the number of times the close date has changed

In the field update use a formula to specify the new field value. The formula is the name of your custom field + 1. Save the field update and then go ahead and activate your workflow rule.

Remember that by default, your new field will only work for new Opportunities. This is because it’s only those Opportunities that will have the starting value of zero in the field you created. On the existing Opportunities the field will be blank.

So firstly test that your new field is working correctly by creating a new Opportunity. Change the Close Date and check that the field advances to 1. Tip: if you’ve followed the above instructions carefully and your field doesn’t update, chances are it’s because you haven’t activated the workflow rule. Activate the rule and create a new Opportunity to re-test the process.

Now you can address the issue of the existing Opportunities. The workflow rule and field update can’t fire retrospectively – in other words you can’t count Close Date changes that have already taken place. But you can count changes from now on.

To do this you need to set the value of your new field to be zero on the existing Opportunities. The Data Loader is one way to do this in bulk. Alternatively, if you’re not familiar with that tool then create a List View on the Opportunity Tab and mass update your new field. Tip: if you’re using Opportunity Record Types you need to include the Record Type as a filter in the List View criteria.

And that (almost) is it. Now you can go ahead and create your report.

Create the dashboard table

One final tip. To display the dashboard table in the way that we’ve shown, add a chart to the report. Format the chart in the way we’ve shown below, not forgetting to group by the Created Date. Then go ahead and create your dashboard table.

And that IS it! We’ve created a YouTube video that shows you exactly how to set up the field, workflow rule and field update. Don’t forget to read our other blog posts on getting the best from Salesforce dashboards.

12 Must-Have Charts For Your Salesforce Dashboard

Download the FREE eBook from our website today

Related Blog Posts

How To Add Products To Opportunities The Easy Way (CPQ Wizard)

How To Add Products To Opportunities The Easy Way (CPQ Wizard)

Many salespeople get frustrated when they try to add products to opportunities in Salesforce. That's because it's cumbersome to find the right products, even (especially) if you're using Salesforce Lightning. For example, the Recent Products list gets in the...

Forecast Categories | What They Mean and How To Use Them

Forecast Categories | What They Mean and How To Use Them

This article tells you everything you need to know about using Forecast Categories to improve revenue forecasting and to analyze the pipeline. In this article, I explain: 1. What a Forecast Category is.2. The way the Forecast Categories and Opportunity Stage fields...

Popular Apps

GSP Target Tracker

Track targets in Salesforce including won and pipeline deals

Popular eBook

Download The 12 Must-Have Dashboard Charts

This fully-illustrated 27 page ebook shows you the 12 Killer Sales Charts for your Dashboard and explains How to Read Them and When to Use Them.

Related Blog Posts


Why You Need To Compare Average Closed Won Opportunity Size


How to use opportunity conversion reports for superior results


How To Stop ‘Closed Lost’ Screwing Up Salesforce Dashboards


5 Easy Tips That Will Make Opportunity Probability Your Trusted Friend

Share This