Forum Discussion
Dynamic allocation cost by period based on Numeric Parameter
Hi Heng ,
Unfortunately dax can't implement dynamic rows.
If you would like to suggest feature improvements, you may vote the idea and comment here to improve this feature. It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Heng ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) Since desktop does not support dynamically changing the number of rows, you will need to add parameters to the power query. Click "transform data" to enter the power query editor, open the "Advanced Editor" and copy and paste the following code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZA/CwIxDMW/S+cLJE3TP+MtUgQX13KDgrg4CTf47W0PhaRbIL+8vPdac0hwetzBs1vclQQqpdhHyoLotqU5H+Fy+2jAy9pnRvwRmGDdn3+CoAY2Chj1nrsCJvtCZg/RG4C0SeyAx9UqBDjvL2UhkxWQHuKtBFCsAEUNJKhSbIagHYz95LBMGYu9pzB3wNkm0CWiHyUNgOPR8vYF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sale Date" = _t, #"Unit Code" = _t, #"Const Cost" = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Repeat("a,",Parameter1-1)&"a"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Sale Date", type date}, {"Unit Code", type text}, {"Const Cost", Int64.Type}, {"Custom", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Custom"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Const Period", each Parameter1),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Sale Date"}, {{"New", each Table.AddIndexColumn(_, "Index", Parameter1, -1, Int64.Type), type table [Sale Date=nullable date, Unit Code=nullable text, Const Cost=nullable number, Const Period=number]}}),
#"Expanded " = Table.ExpandTableColumn(#"Grouped Rows", "New", {"Unit Code", "Const Cost", "Const Period","Index"}, {"Unit Code", "Const Cost", "Const Period","Index"}),
#"Added Custom2" = Table.AddColumn(#"Expanded ", "Const Date", each Date.AddMonths([Sale Date],Parameter1-[Index]+1)),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Monthly Const Cost", each [Const Cost]/Parameter1)
in
#"Added Custom3"
(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.