Dynamic allocation cost by period based on Numeric Parameter

[Heng]

Dear All,
I have sale table and i would like to calculated a measure by allocat cost by construction period parameter selected.
Noted that I want it automatically changed when I change the construction period parameter.
Really appreciated for your help !
Best regards,
Heng
Numeric Parameter: 

Const Period Value = SELECTEDVALUE('Const Period'[Const Period],30)

Sale Table=

Sale Date	Unit Code	Const Cost
01-Feb-23	R15-H176	18500
26-May-23	R15-H125A	30000
07-Aug-23	R11-H43	18500
06-Aug-23	R13-H107	18500
25-Feb-23	R15-H162	18500
11-Feb-23	R05-H20A	18500
24-Jul-23	R11-H81	18500
15-Mar-23	R05-H05A	18500
16-Mar-23	R07-H59	18500
04-Feb-23	R07-H52	18500
19-Aug-23	R13-H99	18500
14-Aug-23	R13-H138	18500
27-Aug-23	R02-H108	36000

My result example of 1 unit code: 

Sale Date	Unit Code	Const Cost	Const Period	Period	Const Date	Monthly Const Cost
01-Feb-23	R15-H176	18500	30	30	01-Mar-23	616.67
01-Feb-23	R15-H176	18500	30	29	01-Apr-23	616.67
01-Feb-23	R15-H176	18500	30	28	01-May-23	616.67
01-Feb-23	R15-H176	18500	30	27	01-Jun-23	616.67
01-Feb-23	R15-H176	18500	30	26	01-Jul-23	616.67
01-Feb-23	R15-H176	18500	30	25	01-Aug-23	616.67
01-Feb-23	R15-H176	18500	30	24	01-Sep-23	616.67
01-Feb-23	R15-H176	18500	30	23	01-Oct-23	616.67
01-Feb-23	R15-H176	18500	30	22	01-Nov-23	616.67
01-Feb-23	R15-H176	18500	30	21	01-Dec-23	616.67
01-Feb-23	R15-H176	18500	30	20	01-Jan-24	616.67
01-Feb-23	R15-H176	18500	30	19	01-Feb-24	616.67
01-Feb-23	R15-H176	18500	30	18	01-Mar-24	616.67
01-Feb-23	R15-H176	18500	30	17	01-Apr-24	616.67
01-Feb-23	R15-H176	18500	30	16	01-May-24	616.67
01-Feb-23	R15-H176	18500	30	15	01-Jun-24	616.67
01-Feb-23	R15-H176	18500	30	14	01-Jul-24	616.67
01-Feb-23	R15-H176	18500	30	13	01-Aug-24	616.67
01-Feb-23	R15-H176	18500	30	12	01-Sep-24	616.67
01-Feb-23	R15-H176	18500	30	11	01-Oct-24	616.67
01-Feb-23	R15-H176	18500	30	10	01-Nov-24	616.67
01-Feb-23	R15-H176	18500	30	9	01-Dec-24	616.67
01-Feb-23	R15-H176	18500	30	8	01-Jan-25	616.67
01-Feb-23	R15-H176	18500	30	7	01-Feb-25	616.67
01-Feb-23	R15-H176	18500	30	6	01-Mar-25	616.67
01-Feb-23	R15-H176	18500	30	5	01-Apr-25	616.67
01-Feb-23	R15-H176	18500	30	4	01-May-25	616.67
01-Feb-23	R15-H176	18500	30	3	01-Jun-25	616.67
01-Feb-23	R15-H176	18500	30	2	01-Jul-25	616.67
01-Feb-23	R15-H176	18500	30	1	01-Aug-25	616.67

[Heng]

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

[v-tangjie-msft · Accepted solution]

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. 

[v-tangjie-msft]

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.