**Introduction**

This one is another use of the almighty Custom summary formula (CSF) in Salesforce reports. Use them – they don’t bite!

**Note**
To appreciate the solution, I hereby request all my readers to try to solve/create the report (described in the use-case below) themselves before jumping to see the solution.
#AwesomeAdmins

**Use Case**
The management has asked for one dashboard/report chart that compares ‘the current month business closed value’ with the ‘moving average for the past 6 months’ for each sales agent. The resulting graph should look like:
rtaimage 

Special thanks to Harshil Shah who rasied this question on the success community here: https://success.salesforce.com/answers?id=9063A000000pCFx

**Solution**

Refer to **Note** first.

Some theory:
The challenge here is to create the desired bar graph that must contain moving average and current month values grouped by opportunity owner. A combination of joint report and CSFs is what I have used to solve this case.

Now to the practical:

Step 1. Create a joint report and add the following filters for the two blocks of “Opportunity” type:
capture

Step 2: Then group the report by Opportunity owner

Step 3: Create CSF for 6 month moving average and place it in the opportunity block 1  space:Capture.JPG
Step 4:  Create another CSF for current month moving average and place it in the opportunity block 2  space: Capture.JPG

The resulting report should look like:
Capture.JPG

Step 5: Add a chart with the following settings:
Capture.JPG

And that’s it! The final report looks like this:
Capture.JPG

**Other information/limitations**

  1. Since this is a dynamic report, meaning its values would change every month, I would recommend creating a report snapshot for this report every month for analytics
  2. The solution is not ideal because of the limitation of Salesforce reports to filter data based on dynamic date ranges only to the following values: 

    In my next post (link to come here) I will present a way to go beyond these dynamic date ranges. eg. Use case: Create a report to display sum of last 5 month sales per user

Cheers!
SalesforceMann

Advertisements