The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Distributor_Code | Division | Prim Item Code | Date | Open Stock | Prim Sales | Sec Sales |
1100794 | Retail | 2000169 | 01-01-2021 | 4588.7 | 0 | 0 |
1100794 | Retail | 2000177 | 01-01-2021 | 2326.4 | 0 | 0 |
1100794 | Retail | 2000172 | 01-01-2021 | 1412.16 | 0 | 0 |
1100794 | Retail | 2000023 | 01-01-2021 | 111.9 | 0 | 0 |
1100794 | Retail | 2000023 | 02-01-2021 | 0 | 0 | 0.003 |
1100794 | Retail | 2000169 | 02-01-2021 | 0 | 0 | 0.43 |
1100794 | Retail | 2000172 | 02-01-2021 | 0 | 0 | 0.195 |
1100794 | Retail | 2000177 | 02-01-2021 | 0 | 0 | 0.514 |
1100794 | Retail | 2000023 | 03-01-2021 | 0 | 0 | 0.092 |
1100794 | Retail | 2000169 | 03-01-2021 | 0 | 0 | 0.2 |
1100794 | Retail | 2000172 | 03-01-2021 | 0 | 0 | 0.291 |
@
Solved! Go to Solution.
Here is a Power Query solution, annotated with comments
let
//change next line to reflect your actual data source
Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Distributor_Code", Int64.Type}, {"Division", type text}, {"Prim Item Code", Int64.Type}, {"Date", type date}, {"Open Stock", type number}, {"Prim Sales", Int64.Type}, {"Sec Sales", type number}}),
//add index column to be able to sort back to original order
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
//Group by "dist code", "division", and "prim item code"
// then add a column to each subgroup for the closing stock based on rules in your question.
#"Grouped Rows" = Table.Group(#"Added Index", {"Distributor_Code", "Division", "Prim Item Code"}, {
{"add cs", (t)=>
Table.FromColumns(
Table.ToColumns(t) &
{List.Generate(
()=>[cs=t[Open Stock]{0} + t[Prim Sales]{0} - t[Sec Sales]{0}, idx = 0],
each [idx] < Table.RowCount(t),
each [cs= let
Open = if t[Open Stock]{[idx]+1} = 0 then [cs] else t[Open Stock]{[idx]+1}
in
Open + t[Prim Sales]{[idx]+1} - t[Sec Sales]{[idx]+1}, idx = [idx]+1],
each [cs])}, {"Distributor_Code", "Division", "Prim Item Code", "Date",
"Open Stock", "Prim Sales", "Sec Sales", "Index", "Closing Stock"})}
}),
//expand the grouped table
//sort back to original order
//remove the Index column
#"Expanded add cs" = Table.ExpandTableColumn(#"Grouped Rows", "add cs", {"Date", "Open Stock", "Prim Sales", "Sec Sales", "Index", "Closing Stock"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded add cs",{{"Date", type date},{"Open Stock", type number}, {"Prim Sales", Int64.Type}, {"Sec Sales", type number}, {"Index", Int64.Type}, {"Closing Stock", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
#"Removed Columns"
Here is a Power Query solution, annotated with comments
let
//change next line to reflect your actual data source
Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Distributor_Code", Int64.Type}, {"Division", type text}, {"Prim Item Code", Int64.Type}, {"Date", type date}, {"Open Stock", type number}, {"Prim Sales", Int64.Type}, {"Sec Sales", type number}}),
//add index column to be able to sort back to original order
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
//Group by "dist code", "division", and "prim item code"
// then add a column to each subgroup for the closing stock based on rules in your question.
#"Grouped Rows" = Table.Group(#"Added Index", {"Distributor_Code", "Division", "Prim Item Code"}, {
{"add cs", (t)=>
Table.FromColumns(
Table.ToColumns(t) &
{List.Generate(
()=>[cs=t[Open Stock]{0} + t[Prim Sales]{0} - t[Sec Sales]{0}, idx = 0],
each [idx] < Table.RowCount(t),
each [cs= let
Open = if t[Open Stock]{[idx]+1} = 0 then [cs] else t[Open Stock]{[idx]+1}
in
Open + t[Prim Sales]{[idx]+1} - t[Sec Sales]{[idx]+1}, idx = [idx]+1],
each [cs])}, {"Distributor_Code", "Division", "Prim Item Code", "Date",
"Open Stock", "Prim Sales", "Sec Sales", "Index", "Closing Stock"})}
}),
//expand the grouped table
//sort back to original order
//remove the Index column
#"Expanded add cs" = Table.ExpandTableColumn(#"Grouped Rows", "add cs", {"Date", "Open Stock", "Prim Sales", "Sec Sales", "Index", "Closing Stock"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded add cs",{{"Date", type date},{"Open Stock", type number}, {"Prim Sales", Int64.Type}, {"Sec Sales", type number}, {"Index", Int64.Type}, {"Closing Stock", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
#"Removed Columns"
Hi @v-jingzhang
Thank you so much for the solution, can you please share the PBIX file too, it would be a bit more helpful for me❤️.
You don't need a PBIX file. As I wrote, all you need to do is replace the lines in your own file after `#"Changed Type#` with those lines in the MCode I supplied.
Or just paste the code below into a blank query using the Advanced Editor in your PBI power query, and it will reproduce everything including the table.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldBBCgIxDAXQu3Q9lv/TtJ1cw+3QhQsXgkvvj9UiyNgpFRJICw/ys22OBLKpW9z5+rjc7nUQAExWJ/BUSyCsD43r6vPr991lOcY577EESV7nsOwxleKZZjQk/GjS2x9Wviw+7YEw3Lrdq291TFvgPqXFiUv3baROxA39uCYTcft2LFvaA2l0pTwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Distributor_Code = _t, Division = _t, #"Prim Item Code" = _t, Date = _t, #"Open Stock" = _t, #"Prim Sales" = _t, #"Sec Sales" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Distributor_Code", Int64.Type}, {"Division", type text}, {"Prim Item Code", Int64.Type}, {"Date", type date}, {"Open Stock", type number}, {"Prim Sales", Int64.Type}, {"Sec Sales", type number}}),
//add index column to be able to sort back to original order
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
//Group by "dist code", "division", and "prim item code"
// then add a column to each subgroup for the closing stock based on rules in your question.
#"Grouped Rows" = Table.Group(#"Added Index", {"Distributor_Code", "Division", "Prim Item Code"}, {
{"add cs", (t)=>
Table.FromColumns(
Table.ToColumns(t) &
{List.Generate(
()=>[cs=t[Open Stock]{0} + t[Prim Sales]{0} - t[Sec Sales]{0}, idx = 0],
each [idx] < Table.RowCount(t),
each [cs= let
Open = if t[Open Stock]{[idx]+1} = 0 then [cs] else t[Open Stock]{[idx]+1}
in
Open + t[Prim Sales]{[idx]+1} - t[Sec Sales]{[idx]+1}, idx = [idx]+1],
each [cs])}, {"Distributor_Code", "Division", "Prim Item Code", "Date",
"Open Stock", "Prim Sales", "Sec Sales", "Index", "Closing Stock"})}
}),
//expand the grouped table
//sort back to original order
//remove the Index column
#"Expanded add cs" = Table.ExpandTableColumn(#"Grouped Rows", "add cs", {"Date", "Open Stock", "Prim Sales", "Sec Sales", "Index", "Closing Stock"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded add cs",{{"Date", type date},{"Open Stock", type number}, {"Prim Sales", Int64.Type}, {"Sec Sales", type number}, {"Index", Int64.Type}, {"Closing Stock", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
#"Removed Columns"
Is this what you want?
Closing Stock is a calculated column with below code.
Closing Stock =
CALCULATE (
SUM ( 'Table'[Open Stock] ) + SUM ( 'Table'[Prim Sales] )
- SUM ( 'Table'[Sec Sales] ),
ALLEXCEPT (
'Table',
'Table'[Distributor_Code],
'Table'[Division],
'Table'[Prim Item Code]
),
'Table'[Date] <= EARLIER ( 'Table'[Date] )
)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @v-jingzhang @Jing, thanks for the answer it works properly, but actually i want to do this calculation in power query. Can you please suggest the solution for power query
Thanks