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 !

[smozgur]

Try this. 

Note, I assumed #5 applied to both #3 and #4 calculations.

let
    ReleaseData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjJU0lEyMNT1SszTNTIwMgbyjA11fROL4DwDIGFoAiYNDZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Release ID" = _t, #"Start Date" = _t, #"End Date" = _t, #"Avg Velocity Points" = _t, #"Total Points" = _t, #"Remaining Points" = _t]),
    Release = Table.TransformColumnTypes(ReleaseData,{{"Release ID", type text}, {"Start Date", type date}, {"End Date", type date}, {"Avg Velocity Points", Int64.Type}, {"Total Points", Int64.Type}, {"Remaining Points", Int64.Type}}),
    
    SprintData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjZU0lEKAhHGBkqxOkABI3QBY5gAhGuCyjVF5ZohuLEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sprint ID" = _t, #"Release ID" = _t, Done = _t]),
    
    Sprint = Table.TransformColumnTypes(SprintData,{{"Sprint ID", type text}, {"Release ID", type text}, {"Done", Int64.Type}}),

    MainIndex = Table.AddIndexColumn(Sprint, "MainIndex", 0, 1, Int64.Type),
    JoinReleases = Table.ExpandTableColumn(Table.NestedJoin(MainIndex, {"Release ID"}, Release, {"Release ID"}, "Release", JoinKind.LeftOuter), "Release", {"Avg Velocity Points", "Remaining Points"}),
    TotalDone = Table.AddColumn(JoinReleases, "TotalDone", each List.Sum(MainIndex[Done])),
    NewSprints = Table.FirstN(Table.SelectRows(TotalDone, each ([Done] = null)), 2),
    SubIndex = Table.AddIndexColumn(NewSprints, "SubIndex", 0, 1, Int64.Type),
    Cumulative = Table.AddColumn(SubIndex, "Cumulative Forecast", each 
        let 
            Result = [TotalDone] + [Avg Velocity Points] * (1 + Number.From([SubIndex] = 1) )
            in if Result > [Remaining Points] then [Remaining Points] else Result),
    Merge = Table.ExpandTableColumn(Table.NestedJoin(MainIndex, "MainIndex", Cumulative, "MainIndex", "Cumulative"), "Cumulative", {"Cumulative Forecast"}),
    Result = Table.RemoveColumns(Table.Sort(Merge,{{"MainIndex", Order.Ascending}}), "MainIndex")
in
    Result

[smozgur]

For multiple releases in the Release table (as it is expected).

Simply set the Release and Sprint identifiers with your actual tables (and remove ReleaseData and SprintData), then it should work with the real data.

let
    ReleaseData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjJU0lEyMNT1SszTNTIwMgbyjA11fROL4DwDIGFoAiYNDZRidYB6jAjogag2ApEWQC2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Release ID" = _t, #"Start Date" = _t, #"End Date" = _t, #"Avg Velocity Points" = _t, #"Total Points" = _t, #"Remaining Points" = _t]),
    Release = Table.TransformColumnTypes(ReleaseData,{{"Release ID", type text}, {"Start Date", type date}, {"End Date", type date}, {"Avg Velocity Points", Int64.Type}, {"Total Points", Int64.Type}, {"Remaining Points", Int64.Type}}),

    SpritData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjZU0lEKAhHGBkqxOkABI3QBY5gAhGuCyjVF5ZqhcsGmg0w0QjbdCN10ZAETmACS+Uao5kO4sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sprint ID" = _t, #"Release ID" = _t, Done = _t]),
    Sprint = Table.TransformColumnTypes(SpritData,{{"Sprint ID", type text}, {"Release ID", type text}, {"Done", Int64.Type}}),    

    Source = Table.NestedJoin(Release, {"Release ID"}, Sprint, {"Release ID"}, "Sprint", JoinKind.LeftOuter),

    MergeSprints = Table.TransformColumns(Source, {"Sprint", 
            (Sprint) => let
                    MainIndex = Table.AddIndexColumn(Sprint, "MainIndex", 0, 1, Int64.Type),
                    JoinReleases = Table.ExpandTableColumn(Table.NestedJoin(MainIndex, {"Release ID"}, Release, {"Release ID"}, "Release", JoinKind.LeftOuter), "Release", {"Avg Velocity Points", "Remaining Points"}),
                    TotalDone = Table.AddColumn(JoinReleases, "TotalDone", each List.Sum(MainIndex[Done])),
                    NewSprints = Table.FirstN(Table.SelectRows(TotalDone, each ([Done] = null)), 2),
                    SubIndex = Table.AddIndexColumn(NewSprints, "SubIndex", 0, 1, Int64.Type),
                    Cumulative = Table.AddColumn(SubIndex, "Cumulative Forecast", each 
                        let 
                            Result = [TotalDone] + [Avg Velocity Points] * (1 + Number.From([SubIndex] = 1) )
                            in if Result > [Remaining Points] then [Remaining Points] else Result),
                    Merge = Table.ExpandTableColumn(Table.NestedJoin(MainIndex, "MainIndex", Cumulative, "MainIndex", "Cumulative"), "Cumulative", {"Cumulative Forecast"}),
                    Result = Table.RemoveColumns(Table.Sort(Merge,{{"MainIndex", Order.Ascending}}), "MainIndex")
                in
                    Result
            }),
    RemoveRelease = Table.RemoveColumns(MergeSprints, Table.ColumnNames(Release)),
    Result = Table.ExpandTableColumn(RemoveRelease, "Sprint", {"Sprint ID", "Release ID", "Done", "Cumulative Forecast"})
in
    Result