Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello everyone
I need to calculate a stock forecast in columns Qty and Value.
Table before calculation
Material | YearMonth | RowNumber | StockQty | StockValue | PurchasingQty | PurchasingValue | SalesQty |
AAAA | 202410 | 1 | 10 | 15 | 3 | 4 | 8 |
AAAA | 202411 | 2 | 7 | 8 | 3 | ||
AAAA | 202501 | 3 | 4 | ||||
AAAA | 202507 | 4 | 8 | 5 | |||
BBBB | 202410 | 1 | 3 | 4 | |||
BBBB | 202411 | 2 | 4 |
Table after calculation
Material | YearMonth | RowNumber | StockQty | StockValue | PurchasingQty | PurchasingValue | SalesQty | Qty | Value |
AAAA | 202410 | 1 | 10 | 15 | 3 | 4 | 8 | 5 | 7,30769231 |
AAAA | 202411 | 2 | 5 | 7,307692308 | 7 | 8 | 3 | 9 | 11,4807692 |
AAAA | 202501 | 3 | 9 | 11,48076923 | 4 | 5 | 6,37820513 | ||
AAAA | 202507 | 4 | 5 | 6,378205128 | 8 | 5 | 13 | 11,3782051 | |
BBBB | 202410 | 1 | 3 | 4 | 3 | 4 | |||
BBBB | 202411 | 2 | 3 | 4 | 4 | -1 | -1,33333333 | ||
CCCC | 202410 | 1 | 3 | 5 | 8 | -5 | -8,33333333 |
The first row by Material can have a StockQty and Value or not.
By Material, each next row is using Qty result in StockQty and Value result in StockValue from previsous row, as shown in Bold.
Calculation is done by Material after sort by RowNumber. or YearMonth:
The logic corresponds to Periodic Unit Price calculation in SAP, that is different from Moving Average Price:
I need to do this in Power Query, not in Power BI.
I have tried to use the logic from several topics but I struggle to manage it working. Here are the topics
Grouped Running Totals: https://www.myonlinetraininghub.com/grouped-running-totals-in-power-query
Use Previous Rows Value in Current Calculation: https://community.fabric.microsoft.com/t5/Desktop/Power-Query-Use-Previous-Rows-Value-in-Current-Cal...
Refer to next row to perform calcuation in power Query: https://community.fabric.microsoft.com/t5/Power-Query/Refer-to-next-row-to-perform-calcuation-in-pow...
I understand that due to the specific calculation, I cannot use Grouped Runing Totals
Thanks a lot in advance for your help.
Christophe
Solved! Go to Solution.
Hi,
Thanks for PhilipTreacy 's and Omid_Motamedise 's concern about the problem, and i want to offer some more information for user to refer to.
hello @Christophe11 , you can create a blank query and put the following code in advanced editor in power query to calculate the qty.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgQCJR0lIwMjE0MDIMMQhMEMUyBhDMQmQGyhFKuDqhakzgiIIcgcrAakHlWdqYEh1BQIUoBiEwx15lCbIAhklilYLUidExCguxFmEsyFmCpRXYhkcywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Material = _t, YearMonth = _t, RowNumber = _t, StockQty = _t, StockValue = _t, PurchasingQty = _t, PurchasingValue = _t, SalesQty = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", type text}, {"YearMonth", Int64.Type}, {"RowNumber", Int64.Type}, {"StockQty", Int64.Type}, {"StockValue", Int64.Type}, {"PurchasingQty", Int64.Type}, {"PurchasingValue", Int64.Type}, {"SalesQty", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"StockQty", "StockValue", "PurchasingQty", "PurchasingValue", "SalesQty"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Qty", each let a=[Material],
b=[RowNumber],
c=Table.SelectRows(#"Replaced Value",each [Material]=a and [RowNumber]<=b),
d=List.Sum(c[StockQty])+List.Sum(c[PurchasingQty])-List.Sum(c[SalesQty])
in d),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each try Table.SelectRows(#"Added Custom",(x)=>x[Material]=[Material] and x[RowNumber]=[RowNumber]-1)[Qty]{0} otherwise [StockQty]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Value", each let
a=[RowNumber],
BufferedTable = Table.Buffer(Table.SelectRows(#"Added Custom1",(x)=>x[Material]=[Material])),
InitialValue = BufferedTable{0}[StockValue]+ BufferedTable{0}[PurchasingValue]-BufferedTable{0}[SalesQty]*( BufferedTable{0}[StockValue]+ BufferedTable{0}[PurchasingValue])/(BufferedTable{0}[Custom]+BufferedTable{0}[PurchasingQty]),
CustomColumnValues = List.Generate(
() => [prevValue = InitialValue, idx = 1],
each [idx] <= Table.RowCount(BufferedTable),
each [prevValue = [prevValue] + BufferedTable{[idx]}[PurchasingValue]-BufferedTable{[idx]}[SalesQty]*( [prevValue] + BufferedTable{[idx]}[PurchasingValue])/(BufferedTable{[idx]}[Custom]+BufferedTable{[idx]}[PurchasingQty]),idx = [idx] + 1],
each [prevValue]
)
in CustomColumnValues{a-1}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom"})
in
#"Removed Columns"
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
let
moving_average = (tbl) =>
[
rows = List.Buffer(Table.ToRecords(Table.Sort(tbl, "RowNumber"))),
gen = List.Generate(
() =>
[
i = 0,
r = rows{0},
stock = [StockQty = r[StockQty], StockValue = r[StockValue]],
close =
[
Qty = stock[StockQty] + r[PurchasingQty] - r[SalesQty],
Value = r[StockValue] + r[PurchasingValue]
- r[SalesQty] * (r[StockValue] + r[PurchasingValue]) / (r[StockQty] + r[PurchasingQty])
]
],
(x) => x[i] < List.Count(rows),
(x) =>
[
i = x[i] + 1,
r = rows{i},
stock = Record.RenameFields(x[close], {{"Qty", "StockQty"}, {"Value", "StockValue"}}),
close =
[
Qty = stock[StockQty] + r[PurchasingQty] - r[SalesQty],
Value = stock[StockValue] + r[PurchasingValue]
- r[SalesQty] * (stock[StockValue] + r[PurchasingValue]) / (stock[StockQty] + r[PurchasingQty])
]
],
(x) => x[r] & x[stock] & x[close]
)
][gen],
// replace Excel.CurrentWorkbook with a ref to your table
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
zero = Table.TransformColumns(Source, {}, (x) => if List.Contains({" ", "", null}, x) then 0 else x),
group = Table.Group(zero, "Material", {"x", moving_average}),
result = Table.FromRecords(List.Combine(group[x]))
in
result
Thanks a lot, 2 solutions are working, I have learned a lot from you! Very useful, thanks again!
Hi,
Thanks for PhilipTreacy 's and Omid_Motamedise 's concern about the problem, and i want to offer some more information for user to refer to.
hello @Christophe11 , you can create a blank query and put the following code in advanced editor in power query to calculate the qty.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgQCJR0lIwMjE0MDIMMQhMEMUyBhDMQmQGyhFKuDqhakzgiIIcgcrAakHlWdqYEh1BQIUoBiEwx15lCbIAhklilYLUidExCguxFmEsyFmCpRXYhkcywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Material = _t, YearMonth = _t, RowNumber = _t, StockQty = _t, StockValue = _t, PurchasingQty = _t, PurchasingValue = _t, SalesQty = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", type text}, {"YearMonth", Int64.Type}, {"RowNumber", Int64.Type}, {"StockQty", Int64.Type}, {"StockValue", Int64.Type}, {"PurchasingQty", Int64.Type}, {"PurchasingValue", Int64.Type}, {"SalesQty", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"StockQty", "StockValue", "PurchasingQty", "PurchasingValue", "SalesQty"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Qty", each let a=[Material],
b=[RowNumber],
c=Table.SelectRows(#"Replaced Value",each [Material]=a and [RowNumber]<=b),
d=List.Sum(c[StockQty])+List.Sum(c[PurchasingQty])-List.Sum(c[SalesQty])
in d),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each try Table.SelectRows(#"Added Custom",(x)=>x[Material]=[Material] and x[RowNumber]=[RowNumber]-1)[Qty]{0} otherwise [StockQty]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Value", each let
a=[RowNumber],
BufferedTable = Table.Buffer(Table.SelectRows(#"Added Custom1",(x)=>x[Material]=[Material])),
InitialValue = BufferedTable{0}[StockValue]+ BufferedTable{0}[PurchasingValue]-BufferedTable{0}[SalesQty]*( BufferedTable{0}[StockValue]+ BufferedTable{0}[PurchasingValue])/(BufferedTable{0}[Custom]+BufferedTable{0}[PurchasingQty]),
CustomColumnValues = List.Generate(
() => [prevValue = InitialValue, idx = 1],
each [idx] <= Table.RowCount(BufferedTable),
each [prevValue = [prevValue] + BufferedTable{[idx]}[PurchasingValue]-BufferedTable{[idx]}[SalesQty]*( [prevValue] + BufferedTable{[idx]}[PurchasingValue])/(BufferedTable{[idx]}[Custom]+BufferedTable{[idx]}[PurchasingQty]),idx = [idx] + 1],
each [prevValue]
)
in CustomColumnValues{a-1}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom"})
in
#"Removed Columns"
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello, thanks for your answers, I have modified the first post to include all information for precisions.
let
moving_average = (tbl) =>
[
rows = List.Buffer(Table.ToRecords(Table.Sort(tbl, "RowNumber"))),
gen = List.Generate(
() =>
[
i = 0,
r = rows{0},
stock = [StockQty = r[StockQty], StockValue = r[StockValue]],
close =
[
Qty = stock[StockQty] + r[PurchasingQty] - r[SalesQty],
Value = r[StockValue] + r[PurchasingValue]
- r[SalesQty] * (r[StockValue] + r[PurchasingValue]) / (r[StockQty] + r[PurchasingQty])
]
],
(x) => x[i] < List.Count(rows),
(x) =>
[
i = x[i] + 1,
r = rows{i},
stock = Record.RenameFields(x[close], {{"Qty", "StockQty"}, {"Value", "StockValue"}}),
close =
[
Qty = stock[StockQty] + r[PurchasingQty] - r[SalesQty],
Value = stock[StockValue] + r[PurchasingValue]
- r[SalesQty] * (stock[StockValue] + r[PurchasingValue]) / (stock[StockQty] + r[PurchasingQty])
]
],
(x) => x[r] & x[stock] & x[close]
)
][gen],
// replace Excel.CurrentWorkbook with a ref to your table
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
zero = Table.TransformColumns(Source, {}, (x) => if List.Contains({" ", "", null}, x) then 0 else x),
group = Table.Group(zero, "Material", {"x", moving_average}),
result = Table.FromRecords(List.Combine(group[x]))
in
result
Hard to help without the file but sounds like you are trying to do running total - see if these help
Quickly Create Running Totals in Power Query
Grouped Running Totals in Power Query
Regards
Phil
Proud to be a Super User!
File is deleted and there is no access into it but you can use Python or R in Power query fut such mathematical model
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
68 | |
25 | |
18 | |
12 |