Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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"
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"
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
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.
It seems smpa01's workaround is good.
Regards
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.
It seems you need to expend the table with the icon.
Regards,
User | Count |
---|---|
104 | |
92 | |
88 | |
78 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |