Forum Discussion
Helper IIS 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:
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
- lbendlin
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-Forum/ba-p/963216
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/1447523
Fali324Current 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
- 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
