Ask Search:
Anna HebererAnna Heberer 

Summarize Contract value by month

Hi Community,

I would like to create a report based on the Contract object that shows the monthly value sum of every contract that was active in each month starting with Jan 2016.

In the Contract object, I have Start Date, End Date and Monthly Recurring Revenue (CMRR) fields. Ideally I should be able to create a formula that checks which contracts were active in each month and summarize their CMRR. This is what I currently do in Excel by exporting the data from Salesforce but my manager wants it to be available as a live report in SFDC.

I don't see this being possible with a report formula because this function only allows numbers, percents and currencies but no dates.

I'm wondering if it could be solved with a hidden formula field in the Contract object but I'm not sure what kind of formula to use there.

If you know of an app that offers some extended reporting functions that would help me solve this issue I'd gladly look into it, but I wasn't able to find one myself.

Thanks and regards,
Anna
Best Answer chosen by Anna Heberer
Deanne WaltersDeanne Walters
Since you want to have one record included in multiple months in the report. I do not think it would be possible to do it with how you have it now.

However, what you could do is create a child or related object called monthly revenue. You can have Process Builder automatically create a record for each month with the monthly revenue. Then you can report on that to give you a break down by each month.

All Answers

Deanne WaltersDeanne Walters
Do you want one report for each month or one report for all of the months?

If excel how do you deal if a contract goes for multiple months?

In Salesforce after you group by a date field you can change the grouping to group by month or year. Would that be what you want?
Anna HebererAnna Heberer
Hi Deanne,

I need a report for all of the months beginning with Jan 2016. In Excel I have a formula that checks which contracts were active in any given month based on their start and end dates and then it sums up their monthly recurring values, thus showing how much recurring monthly revenue we had in each month starting with Jan 2016. 

So what I'm looking for is a monthly recurring revenue report for the past two years and the current one, and the information I have are contract start date, contract end date and contract monthly recurring value. 

Kind regards,
Anna 
Deanne WaltersDeanne Walters
Since you want to have one record included in multiple months in the report. I do not think it would be possible to do it with how you have it now.

However, what you could do is create a child or related object called monthly revenue. You can have Process Builder automatically create a record for each month with the monthly revenue. Then you can report on that to give you a break down by each month.
This was selected as the best answer
Anna HebererAnna Heberer
Hi Deanne,

 that is just the kind of clever solution I was looking for! I should be able to build something like that in our system. Thank you very much for your suggestion!

Kind regards,
Anna