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
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 !
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
67 | |
22 | |
18 | |
12 |