Sales Pipeline / Forecast for future 12 months - HELP!

[Gingerjeans88]

Hello all,

Please help, I am SO stuck. 

I need a column chart that displays, by month, the sum of all individual opportunities’ sales values to give a variable forecast of revenue in the pipeline over the next 12 - 24 months. 

As an example, an opportunity closing (or expected to close) in Jan 2020 will need to have its revenue split evenly over the 12 months following its close date. And I need to do this for every opportunity at a status of OPEN or WON and sum them together by month. 

Table example is as follows, with only necessary columns shown:

Opportunities Table

Opportunity Name	Weighted Revenue	Close Date
30000 units of oil for chip shop	£150,000	15 January 2020
500kilograms of flour for bakery	£2,400	20 August 2019
10000kilograms of almonds	£48,000	5 March 2019

So I would like to see, for example, the £150,000 split evenly across 12 months starting from and including Jan 2020 until and including December 2021.

Likewise, I would want to see the £2,400 split evenly across 12 months from and including August 2019 until and including July 2020.

THEN I need this to happen for all open or won opportunities still within the 12 months and have them summed together by month and displayed in a column chart like below. 

Please help, I have no idea how to dynamically add the future 12 months based on the opportunity's close date (date table?) or how to sum up that evenly split monthly calculation of all opportunities' revenue. 

@Greg_Deckleryou are such a whizz I am really hoping you can help me. 

[parry2k]

@Gingerjeans88 solution attached, I think this is what you are looking for.

[Gingerjeans88]

@parry2k this is incredible and I think it is excactly what I need! Just a quick question to help me replicate it.

Your step 'Extended End Date'....what have you done there? Is that just to simulate having more rows of data for the example report? 

Just checking it is something dynamic in my source Opportunity table and nothing I will have to do manually ongoing.

Thanks so much again!

[parry2k]

Yes it is dynamic and previous steps adds 12 months to the date and make a list and this step expand that date to rows. There will be no manual process. Hope this helps.

[Gingerjeans88]

Got it, thank you!
Last question - my sum seems over-inflated and, looking at the data, it seems it’s not just adding a new monthly value, but the expanded list is adding that monthly value to every day of every month.

So instead of having 12 values of £12,500 for each of the months in the forecast, each day has £12,500. How do I fix this?
Or Is this mitigated somehow by grouping when adding to the chart?

[parry2k]

@Gingerjeans88 not sure if I following your question, if total oppy revenue is 12000 and for each month it would be 1000 and there is already a column I added for this (1000 value) which you should be using. Am I missing something here?

[Gingerjeans88]

No you're not at all, it's me! 

So say as an example, the first opportunity.

10000kilograms of almonds

£48,000

15 March 2019 2020

With having applied the changes you so kindly gave, the £48,000 total oppy revenue is divided by 12 into £4,000.

However, I am seeing the £4,000 367 times instead of once for each month. Hence in your pbix March 2019's revenue is £68,000 (£4,000 x 17 days) instead of just £4,000....and there are no other opportunities with a value in March, so that should be the total monthly value for March.

I'm sure it is easily fixed and something I did wrong, but thought I'd ask anyway! Unfortunately I can't share the pbix but let me try some screenshots....

Thanks @parry2k !! Can't explain how helpful this has been

[parry2k · Accepted solution]

@Gingerjeans88 I see what you are saying, sorry about that. it is giving revenue for each day, anyhow, see attached and I think that will do it

Would appreciate Kudos 🙂 if my solution helped.

[Gingerjeans88]

@parry2k I honestly cannot thank you enough for this, it is spot on! Very much appreciated.

[Greg_Deckler]

@Gingerjeans88  - I just saw this, looks like you have been helped by @parry2k . If you need anything else let me know. Seems like you need a variation of Open Tickets. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364.

[Gingerjeans88]

@parry2k you are fantastic, a massive thanks for the help and speedy reply! It’s after midnight here and I’ve just logged off so as soon as I’m back on the laptop in the morning I’m going to have a look!