Forecasting on weekly basis

[Anonymouspanda]

Hi everyone, I have an issue about doing a loop by using DAX language. I need to calculate the opening and closing stock for forecasting of each material in every week. For instance, the opening stock of material A in week 1 + pallets issued in - pallets issued out = closing stock of material A in week 1. This closing stock of material A in weel 1 will be the opening stock of material A in week 2 and so on. The assumption is that there isnt any pallets to begin with. Cheers!

[smpa01 · Accepted solution]

Are you looking for this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYgMgNlWK1YlWMoKKgGSMwSLGUBEjsDqQCEjOCUXECCpiABaF6YKJGMN1OQOxCVyNEVTEFG6OMVTEECISCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Week = _t, Material = _t, Out = _t, In = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}, {"Out", Int64.Type}, {"In", Int64.Type}, {"Material", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Material"}, {{"AD", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
   Source=[AD],   
   In_Out= Table.AddColumn(Source,"In-Out", each [In]-[Out]),
   X=List.Skip(List.Accumulate(In_Out[#"In-Out"],{0},(state,current)=>state&{List.Last(state)+current})),
   Y= Table.FromColumns(Table.ToColumns(In_Out)&{X}),
   Z= Table.AddColumn(Y,"OB", each [Column6]-[Column4]+[Column3]),
   #"Renamed Columns" = Table.RenameColumns(Z,{{"Column1", "Week"}, {"Column2", "Material"}, {"Column3", "Out"}, {"Column4", "In"}, {"Column6", "CB"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column5"}),
  #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Week", "Material", "Out", "In", "OB", "CB"})
in
    #"Reordered Columns"),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Week", "Material", "Out", "In", "OB", "CB"}, {"Week", "Material", "Out", "In", "OB", "CB"})
in
    #"Expanded Custom"

[v-cherch-msft]

Hi @Anonymouspanda 

Could you explain more about your expected output with numbers?How to get the Opening Stock column?Please check if this post could help you.If it is not your case,please refer to How to Get Your Question Answered Quickly.

Regards,

Cherie

[Anonymouspanda]

Hi Cherie

The value in the Opening Stock column will be 0 and the Closing Stock is Opening Stock + Pallets Issued In - Pallets Issued Out (For all the materials in week 1). The week 2 Opening Stock for each of the materials will be their respective week 1 Closing Stock and the week 2 Closing Stock is the week 2 Opening Stock + Pallets Issued In - Pallets Issued Out.

[v-cherch-msft]

Hi @Anonymouspanda 

It seems smpa01's workaround is good.

Regards

[Anonymouspanda]

Hi Cherie,

How to remove the table below, modify the code provided by smpa01, and get the values shown on the Opening Stock Column. Here is my original table for your reference.

[v-cherch-msft]

Hi @Anonymouspanda 

It seems you need to expend the table with the icon.

Regards,