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
Anonymous
Not applicable

Spread project value evenly accross project duration

Essentially i have a table similar to below:

 

ProjectStartEndValueRevenue Shape
ABC01/02/202001/04/2022$100,000,000.00Flat
ZYX01/01/201801/02/2029$310,000,000.00Flat
MNO01/06/202101/07/2024$75,000,000.00S-Curve
TUV01/10/201901/02/2025$150,000,000.00S-Curve

 

Flat - Evenly distributed revenue per month

S-Curve - Variable, but essentially a normal distribution of revenue across duration.

 

I need to create a monthly revenue line chart from the above data.

 

My first assignment is to assume all the "Revenue Shape" is flat, and to evenly distribute all revenue on a line chart.

 

Second assignment is to figure out a standard "S-Curve" or normal distribution for forecasting purposes, and to implement this where the "Revenue Shape" equals S-Curve.

 

I cannot seem to get anything to work, any advice would be appreciated.

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.  I changed the start and end dates to check my result.

Hope this helps.

Untitled.png


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

View solution in original post

16 REPLIES 16
Ashish_Mathur
Super User
Super User

Hi,

I can help you get the monthly revenue projection where the revenue shape is flat of all projects.  Would you be OK with that starting point? 


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

Hi Ashish,

 

That would be great!

Thanks

If the start and end dates are not the first of the month, then should the revenue of that month be recognised on proportionate basis or should the revneue still be recognised for the full month?


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

Proportionate would be the best approach

 

Thanks, 

Hi,

You may download my PBI file from here.  I changed the start and end dates to check my result.

Hope this helps.

Untitled.png


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

Ashish,

Thank you for sharing this example. It was exactly what I was looking for.

Caleb

 

You are welcome.


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

@Ashish_Mathur Hi, Ashish! This solution proved very useful for something I'm working on, but occasionally I'm getting duplicate rows. Instead of revenue, I'm spreading labor forecasts through projects with multiple phases & dates so that may be why. I can't find where that's happening in the calculations. Any ideas or do I need to put my data here? 

Hi,

Share the download link of your PBI file, explain the question and show the expected result.


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

@Ashish_Mathur Sorry for the delay - it took me a while to format everything and get it out of my test files! Here's the link to the test file. One table is the current state, and the other is the desired state. What I am aiming to do is take phases of a project with different start and end dates and evenly spread out the projected labor across resources. 

 

https://schmidtassociates-my.sharepoint.com/:u:/g/personal/lbudack_schmidt-arch_com/EUswp4Rquf1Cm7xg... 

Hi,

I cannot understand your requirement at all.


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

Is the end date (month) inclusive or exclusive?

For the normal distribution - what is the value for the first and last month?

Anonymous
Not applicable

It would be inclusive, they are usually not the first of the month, but that's just what i put in the table above.

 

There is literally no defined values to start or end, I just know that some contract types would more likely have an "S-Curve" revenue shape, but I would like to have an "outlook" of revenue.
Would really just need to be some basic standard we apply like over 12 months it could be:

 

Jan2%
Feb5%
Mar7%
Apr10%
May12%
Jun14%
Jul14%
Aug12%
Sep10%
Oct7%
Nov5%
Dec2%

So the initial value would be 0.02 .

But your sample table only has 12 values. You said inclusive, which would mean 13 months for your examples. Sooo - which one is it?

Anonymous
Not applicable

The amount of months would be variable, i just gave an example of the distribution over 12.
Month count could be 6, 12, 13, 48, 49 etc. etc.

Please provide sanitized sample data that fully covers your issue. 

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.