Forum Discussion
Tricky Revenue Formula
I have a table that looks like this:
Which is a very small subset of data I've filtered for this example. So I was originally tasked with coming up with a formula that looked at the last 3 months of revenue for a company, summed it, and multiplied the value by 4. That value would then be applied to whoever owns the account currently. Formula looks like this:
And it works as intended but I've been thrown a new loop. Rather than giving ALL revenue to whoever currently owns the account, I've been tasked with ensuring the money is split between the managers during the time they managed the account. So in the top screenshot, all invoice dates would have to fall between the [entry date] and [exit date] fields.
The reason for this is because in a 3 month period, an account could switch from one person to another. So we want to make sure that if for instance the above company made $50,000 in 3 months and halfway through that 3 month period the account was handed to a new person, the old manager would have $25,000 recorded and the new person would also have recorded $25,000 rather than just saying whoever currently owns the account made $50,000 on it.
So the updated formula would need to:
1. Dynamically calculate the sum of revenue for a company going back 3 months from today
2. Account for any account manager switches (if any) and split the revenue accordingly based on their entry/exit dates
3. Multiply the grand total by 4 to return an "annual amount"
But it's rather complex to put together and not sure where to start :smileyhappy: as you can see in the top screenshot, entries are currently repeating due to it assigning all invoice dates to each account manager. Just wanted to show the raw table... Also, this account has years of invoice dates, just wanted to show an example of an account that had multiple changes over the years. Some accounts may not have any manager changes at all.
2 Replies
Hi Anonymous,
Account for any account manager switches (if any) and split the revenue accordingly based on their entry/exit dates
Could you describe the expected result with some examples? It's hard to imagine what you want to achieve.
Regards,
Yuliana Gu
v-yulgu-msft Sure I will break this down using an example of one company (There are thousands)
1. From Salesforce, we have delta accounts with their respective account managers (not every account has a manager):
2. From our SQL database, we have a table of the history of revenue generated from a given company:
Based on these 2 data sources, I created a one to many relationship based on the company ID field:
3. I was asked to build a formula that would calculate the sum of revenue for the past 3 months for all companies and multiply by 4:
So for EVERY company that Latricia Collins is the account manager for in Salesforce, she will get a total using the above formula using the revenue from our SQL database (This currently works as intended):
THE NEW CHALLENGE:
5. So an account can be switched to another manager at any time. There is another table in our SQL database that shows these changes over time:
As you can see, Latricia is the CURRENT account manager for this company and there were 2 different ones in the past.
What I'm now being asked to do, is still use a formula that calculates the sum of all revenue from the past 3 months, while accounting for any possible account manager switches within that 3 month period.
So let's say you were the AM for 1.5 months and I was the AM for the remaining 1.5 months in the past 3 months. Currently if my name is on the account in Salesforce, the money that was made under you and under me would just appear 100% under me.
I need to make a formula that will still give you the $ amount that you made while you had the account and giving me the $ amount that was made AFTER the account was handed to me.
So if the last 3 months have been 8/8/2018 - 11/8/2018 and let's say you were the account owner up until 9/15/2018, the revenue made on the account using invoice dates between that period would go to you, and 9/15/2018-11/8/2015 invoice dates would go to me. So if the account made a flat $20,000 for example in the last 3 months, $10,000 goes to you and $10,000 goes to me rather than just saying I made $20,000 on the account because I'm the current account manager.
