Forum Discussion
Helper IIS Curve model HELP
Super UserPlease 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
Helper II
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