March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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 everyone,
I'm having trouble creating a simple recursive formula that allows me to reference a newly created column value as the starting value in a subsequent calculation. See the excel based screenshot below — I need to create a summarized End_Value based on activity during the Month, then conditionally reference that End_Value by ID in order to use it as the Start_Value in the following Month. I've highlighted month 1 ending values and month 2 starting values in green for illustrative purposes. Any advice here would be greatly appreciated!
Solved! Go to Solution.
Hi @djburch15, check this:
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7RCcAwCEXRXfxOQJ9Ih5Hsv0aN2iIk/hy8iTuJCC3KyxwTFmOL0VlOABqRKHds40RVbTT+17cUVvZux3myCR7Nltz8pIIt/Y9Z01mz+cMWS1EbtRZBPRR0Xg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Month = _t, Start_Value = _t, Adds = _t, Removals = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Month", Int64.Type}, {"Start_Value", type number}, {"Adds", type number}, {"Removals", type number}}),
AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),
fn_EndValue =
(myTable as table)=>
[ a = Table.Buffer(Table.SelectColumns(myTable, {"Start_Value", "Adds", "Removals"})),
lg =
List.Generate(
()=> [ x=0, y = List.Sum(Record.ToList(a{x})) ],
each [x] < Table.RowCount(a),
each [ x = [x]+1, y = [y] + List.Sum(Record.ToList(a{x})) ],
each [y]
),
b = Table.FromColumns(Table.ToColumns(myTable) & {lg}, Value.Type(myTable & #table(type table[End_Value=number],{}) ) )
][b],
GroupedRows = Table.Group(AddedIndex, {"ID"}, {{"All", each fn_EndValue(_), type table}}),
CombinedAll = Table.Combine(GroupedRows[All]),
SortedRows = Table.Sort(CombinedAll,{{"Index", Order.Ascending}}),
RemovedColumns = Table.RemoveColumns(SortedRows,{"Index"})
in
RemovedColumns
@dufoq3 this appears to work perfectly when's there a single grouped category, however, I have seven separate grouped categories in practice and the above code doesn't seem to be able to reference the newly calculated Start_Value (End_Value = Adds + Removals) in future months
Hi @djburch15, check this:
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7RCcAwCEXRXfxOQJ9Ih5Hsv0aN2iIk/hy8iTuJCC3KyxwTFmOL0VlOABqRKHds40RVbTT+17cUVvZux3myCR7Nltz8pIIt/Y9Z01mz+cMWS1EbtRZBPRR0Xg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Month = _t, Start_Value = _t, Adds = _t, Removals = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Month", Int64.Type}, {"Start_Value", type number}, {"Adds", type number}, {"Removals", type number}}),
AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),
fn_EndValue =
(myTable as table)=>
[ a = Table.Buffer(Table.SelectColumns(myTable, {"Start_Value", "Adds", "Removals"})),
lg =
List.Generate(
()=> [ x=0, y = List.Sum(Record.ToList(a{x})) ],
each [x] < Table.RowCount(a),
each [ x = [x]+1, y = [y] + List.Sum(Record.ToList(a{x})) ],
each [y]
),
b = Table.FromColumns(Table.ToColumns(myTable) & {lg}, Value.Type(myTable & #table(type table[End_Value=number],{}) ) )
][b],
GroupedRows = Table.Group(AddedIndex, {"ID"}, {{"All", each fn_EndValue(_), type table}}),
CombinedAll = Table.Combine(GroupedRows[All]),
SortedRows = Table.Sort(CombinedAll,{{"Index", Order.Ascending}}),
RemovedColumns = Table.RemoveColumns(SortedRows,{"Index"})
in
RemovedColumns
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
51 | |
26 | |
17 | |
17 |