Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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,
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |