S Curve model HELP

[Fali324]

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:

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. 

[Ashish_Mathur]

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.

[Fali324]

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

[lbendlin]

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

[Fali324]

 Current Ouput

							Prior Years Spend	Dec-23	Jan-24	Feb-24	Mar-24
							Week Ref	115	116	117	118
Project	Start on Site	PC	Start Period	Duration	Forecast Target Budget	Spend to Date	Cost to Go	Dec-23	Jan-24	Feb-24	Mar-24
1	Jan-24	Mar-24	116	3	-£600,000	-£100,000	-£500,000	0	-100000	-247236.4332	-161126.168

Expected output

							Prior Years Spend	Dec-23	Jan-24	Feb-24	Mar-24
							Week Ref	115	116	117	118
Project	Start on Site	PC	Start Period	Project Duration	Forecast Target Budget	Spend to Date	Cost to Go	Dec-23	Jan-24	Feb-24	Mar-24
1	Jan-24	Mar-24	116	3	-£600,000	-£100,000	-£500,000	0	-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 month	Increment
1	0.00403225806	0.001764081	0.001764081
2	0.008064516	0.003485447	0.001721366
3	0.012096774	0.005215381	0.001729935
4	0.016129032	0.006953988	0.001738607
5	0.02016129	0.008701373	0.001747385
6	0.024193548	0.010457644	0.001756271
7	0.028225806	0.012222911	0.001765267
8	0.032258065	0.013997285	0.001774374
9	0.036290323	0.015780881	0.001783596

This is just a snippet