This one is another use of the almighty Custom summary formula (CSF) in Salesforce reports. Use them – they don’t bite!
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.
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:
Special thanks to Harshil Shah who rasied this question on the success community here: https://success.salesforce.com/answers?id=9063A000000pCFx
Refer to **Note** first.
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:
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:
Step 4: Create another CSF for current month moving average and place it in the opportunity block 2 space:
The resulting report should look like:
Step 5: Add a chart with the following settings:
And that’s it! The final report looks like this:
- 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
- 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