Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Fali324
Helper I
Helper I

S Curve model HELP

Hi,

 

I am trying to create a s curve model which redistributes the amounts on a rolling period. Below is a snippet of my data:

Fali324_1-1708685065915.png

 

 

My data points are:
Project name
Start Period
Project Duration
Total Budget
Spend to date
Left to spend.

 

To get the spread value, it is months complete/duration. which then gives me a decimal number to multiply the "left to spend" number to get the spread for the remaining months. I've mocked this up in excel. However, when I enter an actual amount only the next month is recalulated not all the remaining months. Highlighted in yellow are the numbers I'm trying to calculate and the green numbers are my actual.

 

Any help with this will be much appircated as I've been stuck on this for a while. 

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of the Excel file with your formulas intact.  I'll try to convert those Excel formulas into their DAX equivalents.


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

Hi, I'm unable to share the link but have put the details of the table below

lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

 

 Current Ouput

       Prior Years SpendDec-23Jan-24Feb-24Mar-24
       Week Ref115116117118
ProjectStart on SitePCStart PeriodDurationForecast Target BudgetSpend to DateCost to GoDec-23Jan-24Feb-24Mar-24
1Jan-24Mar-241163-£600,000-£100,000-£500,0000-100000-247236.4332-161126.168

 

Expected output

       Prior Years SpendDec-23Jan-24Feb-24Mar-24
       Week Ref115116117118
ProjectStart on SitePCStart PeriodProject DurationForecast Target BudgetSpend to DateCost to GoDec-23Jan-24Feb-24Mar-24
1Jan-24Mar-241163-£600,000-£100,000-£500,0000-100000-178501.5557-321498.444

 

Formula in the cell under FEB- 24 is:
=IFNA(IF(K$18>=$D20,IF(K$18<=$D20+$E20-1,VLOOKUP((K$18-$D20+1)/$E20,'S-Curve Profile'!$B$2:$C$249,2)*($F20-$G20)-(IF(J$18>=$D20,IF(J$18<=$D20+$E20-1,VLOOKUP((J$18-$D20+1)/$E20,'S-Curve Profile'!$B$2:$C$249,2)*($F20-$G20),0),0)),0),0),0)

The S curve sheet is below

 % complete
(months complete / Project Duration)
Accum CF per monthIncrement
10.004032258060.0017640810.001764081
20.0080645160.0034854470.001721366
30.0120967740.0052153810.001729935
40.0161290320.0069539880.001738607
50.020161290.0087013730.001747385
60.0241935480.0104576440.001756271
70.0282258060.0122229110.001765267
80.0322580650.0139972850.001774374
90.0362903230.0157808810.001783596

 

This is just a snippet

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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