Conditional Cumulative Forecast

[yogeshk77]

Hi All,

Really need someone to help me here.

I have two tables 
Release

Another one is Sprint (Related by Release ID column), indicating how many story points are already "Done" in 'Previous sprint(s)'

Here, Sprint S1 & S2 are completed & Sprint S3 is in progress as of now.

Now, I need to a new calculated column in the Sprints table, called "Cumulative Forecast" as seen below

This new colum 'Cuulative Forecast' has following rule

1) This Value should be blank for Sprints that are done (For e.g. Sprint 1 & 2 in this case)

2) 'Cumulative Forecast' should contain value only if 'Done' field is blank

3) First 'Cumulative Forecast' value should be =

'Total of Done' points

+ 'Avg Velocity' (of Release table, related by Release ID column)
For e.g. 90 for Sprint S3
 

4) Successive 'Cumulative Forecast' value should be = 

 'Previous Value' 

+ 'Avg Velocity' (of Release table, related by Release ID column)

5) However, if this value is more than 'Remaining Points' of 'Release' table, then it should be = 'Remaining Points' of Release table

For e.g. Sprint S4 cumulative forecast says 110, instead of 120

6) 'Cumulative Forecast' value should be blank for successive rows (for e.g. Sprint S5 & S6 in this case)

Please provide me with Calculated Column, and not a measure please.

Thank you in advance !