Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Gingerjeans88 solution attached, I think this is what you are looking for.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k I honestly cannot thank you enough for this, it is spot on! Very much appreciated.
@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.
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |