Creating year-over-year-to-date Comparison Forumlas

Have you ever had a metric that you wanted to see on a graph showing where you are this year versus last year?

Business Case

Management has asked to see how the sales team is progressing compared to this same time last year, in a dashboard. They would like to see the number of deals closed this far into the last fiscal year, next to the number of deals closed this fiscal year.

Solution

Write a formula field that examines how far we are into the current fiscal year. Then look at the Close Date of each Opportunity to see how far into the year the deal closed. Finally we should only show the deals that were closed already at the same time in the relative fiscal year. Once the calculations are in place then create a report to show the metrics and finally place that chart on a dashboard for consumption.

Implementation

First off we need to get our formula setup, here is a template;

Example Formula

Field Label: # FY to Date


IF( (Today() - DATE(
IF(Today() < DATE( YEAR( TODAY() ) ,07,01),
YEAR( TODAY() ) -1,
YEAR( TODAY() )
),07,01)) >= (CloseDate - DATE( (IF( CloseDate < DATE( YEAR( CloseDate ) ,07,01),
YEAR( CloseDate ) -1,
YEAR( CloseDate ) )
),07,01)) ,
1,
0
)


Note: This formula assumes that the Fiscal Year begins on July 1 of each year.

Create this formula on the Opportunity object, call the field “# FY To Date“, you do not need to add it to the page layout.

As well if your fiscal year is not the calendar year, we will need to create a formula field that shows the dates in the correct sequence. This field will let us see the months in our fiscal year in the natural order on our chart.

Example Formula

Field Label: Close Date Reporting

CASE( MONTH ( CloseDate ) , 1, "FM07 - January", 2, "FM08 - February", 3, "FM09 - March", 4, "FM10 - April", 5, "FM11 - May", 6, "FM12 - June", 7, "FM01 - July", 8, "FM02 - August", 9, "FM03 - September", 10, "FM04 - October", 11, "FM05 - November", "FM06 - December")


Next create an Opportunity report and add a filter of “# FY To Date” = 1. Then create a chart with a Chart Type of “Line – Group Cumulative”, set the Y-Axis to SUM “# FY To Date” set the X-Axis to “Close Date Reporting” and set Groupings to Fiscal Year.

Now click Run Report and you should see a chart that looks like this.

Now just add that chart to your Sales VP’s favorite dashboard and sit back waiting for the thank you email to arrive in your inbox.

Want more tips like this? Then follow us on Twitter @forcebydesign and @micaiahf.

« On-Demand CRM and Case Tracking for Law Firms: Maximize Client Acquisition and Retention | Home | Useful Salesforce.com Short Hand and Keyboard Shortcuts »