cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

## 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.

1 ACCEPTED SOLUTION
Super User

Hi,

You may refer to my solution here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
6 REPLIES 6
Super User

Hi @erhodes,

Did you get the chance to try out my solution?

Regards,
Ashish Mathur
http://www.ashishmathur.com

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.

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
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.

Super User

Hi,

You may refer to my solution here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors