Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
yogeshk77
Helper I
Helper I

Conditional Cumulative Forecast

Hi All,

Really need someone to help me here.

I have two tables 
Release

yogeshk77_2-1691151521169.png

 

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

yogeshk77_3-1691151566103.png

 

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

 

yogeshk77_4-1691152144594.png

 

 

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 !

2 REPLIES 2
smozgur
Helper I
Helper I

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

 

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

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors