Dynamic allocation cost by period based on Numeric Parameter

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. 

Dear Neeko,
Really appreciate for your great solution. However I want to do it in dax for my scenarion analysis
Kind regards,
Heng