Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
erhodes
Advocate II
Advocate II

Distribute Projected Revenue Annually

I have sales data of open opportunities from our CRM system. I am trying to take the amount from each deal and project out the annual revenue billings for each record. The tables below show an example of the data and an example (truncated to only show columns w/ values in this example) of the required output. 

 

Clients are invoiced annually. So in the example below; 'ABC' is for $1M over 2 years and is expected to close in Oct. 2017 I need the model to show for 'ABC' $500K in Oct-2017, and $500K in Oct-2018. 

 

'GEF' is for $2M over 1.5 years (18months) and is expected to close in Feb. 2018 I need the model to show $1.3M in Feb-2018 and $666K in Feb-2019. 

 

Does anyone have any idea how to accomplish this. I have been having to take the output from CRM and manually break each record into individual records for each year which isn't scalable or a viable long-term solution.  Any help would be greatly appreciated.

 

Thanks. 

Annual Distribution Example.PNG

 

 

 

1 ACCEPTED SOLUTION

Hi,

 

You may refer to my solution here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi @erhodes,

 

Did you get the chance to try out my solution?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Sorry. I didn't see your reply w/ a solution. I am testing it out today and will let you know. Thank you so much for the response.

Ashish_Mathur
Super User
Super User

Hi,

 

I have resolved the problem.  Please give me sometime to post my solution

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mattbrice
Solution Sage
Solution Sage

Do you have a formula for determining what % of contract value is to be shown in a particular year?  How is that determined?

The amount to be shown in a particular year is based on the duration. So a duration of 24 months would divide the total amount by 24 to determine the monthly amount and then multiply the monthly amount by 12 to determine the annual amount. The trick is when the duration is more than 1 year but not full years. for ex. if the duration is 18months than the amount charged at the start of year 1 is the monthly amount *12 while the amount charged at the start of year 2 is the monthly amount *6. 

Hi,

 

You may refer to my solution here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.