Dynamic value replace

[tzanouman]

Hello Team, a bit of help please regarding the following issue. 

I have a table named Inventory with the columns Yearweek, Material_id, Total Demand, Total Stock in the warehouse, and Ending Stock. 

Ending Stock value is just the subtraction between the value of Inventory[Total Demand] - Inventory[Total Stock]. 

What I need is, the new Total Stock of the current week to be replaced by the Ending Stock value of the previous week e.g the Total Stock value of 202415 week will be the Ending Stock of 202414 which is 95.000 items etc. 

Any support on this will be highly appreciated.

Thanks in advance, 

Manolis

Week	Material_ID	Total Demand	Total Stock	Ending Stock
202414	802142	5000	100000	95000
202415	802142	5000	0
202416	802142	5000	0
202417	802142	5000	0
202418	802142	5000	0
202419	802142	5000	0
202420	802142	5000	0
202421	802142	5000	0
202422	802142	5000	0
202423	802142	5000	0
202424	802142	5000	0
202425	802142	5000	0
202426	802142	5000	0
202427	802142	5000	0
202428	802142	5000	0
202429	802142	5000	0
202430	802142	5000	0
202431	802142	5000	0
202432	802142	5000	0
202433	802142	5000	0
202434	802142	5000	0
202435	802142	5000	0
202436	802142	5000	0
202437	802142	5000	0
202438	802142	5000	0
202439	802142	5000	0
202440	802142	5000	0
202441	802142	5000	0
202442	802142	5000	0
202443	802142	5000	0
202444	802142	5000	0
202445	802142	5000	0
202446	802142	5000	0
202447	802142	5000	0
202448	802142	5000	0
202449	802142	5000	0
202450	802142	5000	0
202451	802142	5000	0
202452	802142	5000	0
202501	802142	5000	0
202502	802142	5000	0
202503	802142	5000	0
202504	802142	5000	0
202505	802142	5000	0
202506	802142	5000	0
202507	802142	5000	0
202508	802142	5000	0
202509	802142	5000	0
202510	802142	5000	0
202511	802142	5000	0
202512	802142	5000	0
202513	802142	5000	0
202514	802142	5000	0
202515	802142	5000	0
202516	802142	5000	0
202517	802142	5000	0
202518	802142	5000	0
202519	802142	5000	0
202520	802142	5000	0
202521	802142	5000	0
202522	802142	5000	0

[Ashish_Mathur]

Hi,

Please study my solution to a similar problem in the attached PBI files.

[lbendlin]

 You can use this pseudo code.

RUNNINGTOTAL(SUMX(Total Stock)-SUMX(Demand)))

Here is a Power Query version

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdW9TsNAEEXhV0Fbu5idmZuE3g0FFUgUVhStEhcofxLy+wvTgAtz4sZenXXzNXcYysc4nktXXts0fn22y+Gln0/v96ldnvrx2m6n3+PbdD+ey74biptnzTnszGv6/CEzm1/Vfp6/O1q5s8gbzlvOO87PmN04V87OOTiv0S0yqzmrOas5qzmrBasFqwWrBasFqwWrBasFqwWrBaslqyWrJaslqyWrJaslqyWrJaslq4nVxGpCNRn+LXvwN5rL0FyG5jI0l6G5DM1laK6K5qqsVlmtstrqoCwyq/GWiLdEvCXiLRFviXhL9N+W7L8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Week", Int64.Type}, {"Material_ID", Int64.Type}, {"Total Demand", Int64.Type}, {"Total Stock", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Ending Stock", (k)=> let a=Table.SelectRows(#"Changed Type",each [Week]<=k[Week])
in List.Sum(a[Total Stock])-List.Sum(a[Total Demand]))
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.