Home » Calculate Stock Start End

Calculate Stock Start End

Work out the Start and End Stock for all line items. For first occurrence of Item & Store combination Start Stock = Stock IN End Stock = Start Stock – Stock OUT For succeeding occurrences of Item & Store combination Start Stock = End Stock in previous occurrence End Stock = Start Stock + Stock IN – Stock OUT

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 197
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Calculate Stock Start End with Power Query

Power Query solution 1 for Calculate Stock Start End, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  S = Table.ExpandRecordColumn(
    Table.AddColumn(
      Source, 
      "E", 
      each 
        let
          t = Table.SelectRows(
            Table.FirstN(Source, (r) => r <> _), 
            (r) => r[Item] = [Item] and r[Store] = [Store]
          ), 
          b = (List.Sum(t[Stock IN]) - List.Sum(t[Stock OUT]) ?? 0)
        in
          [S = Byte.From(Table.RowCount(t) = 0) * [Stock IN] + b, E = [Stock IN] - [Stock OUT] + b]
    ), 
    "E", 
    {"S", "E"}, 
    {"Start Stock", "End Stock"}
  )
in
  S
Power Query solution 2 for Calculate Stock Start End, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  S = Table.FromRows(
    List.Accumulate(
      Table.ToRows(Source), 
      {}, 
      (b, n) =>
        let
          s = List.Sum(
            List.Transform(List.Select(b, each {n{1}, n{2}} = {_{1}, _{2}}), each _{3} - _{4})
          )
        in
          b & {n & {s ?? n{3}, n{3} - n{4} + (s ?? 0)}}
    ), 
    Table.ColumnNames(Source) & {"Start Stock", "End Stock"}
  )
in
  S
Power Query solution 3 for Calculate Stock Start End, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Index = Table.AddIndexColumn(Source, "Index"), 
  Group = Table.Group(
    Index, 
    {"Item", "Store"}, 
    {
      "R", 
      each List.Generate(
        () => [a = 0, b = _{a}, c = b[Stock IN], d = c - b[Stock OUT]], 
        (f) => f[a] < Table.RowCount(_), 
        (f) => [a = f[a] + 1, b = _{a}, c = f[d], d = c + b[Stock IN] - b[Stock OUT]], 
        (f) => f[b] & [Opening = f[c], Closing = f[d]]
      )
    }
  ), 
  Table = Table.FromRecords(List.Combine(Group[R])), 
  Sort = Table.Sort(Table, {"Index"}), 
  Return = Table.RemoveColumns(Sort, {"Index"})
in
  Return
Power Query solution 4 for Calculate Stock Start End, proposed by Aditya Kumar Darak 🇮🇳:
Power Query solution 5 for Calculate Stock Start End, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Idx = Table.AddIndexColumn(Source, "Idx", 1, 1), 
  Group = Table.Combine(
    Table.Group(
      Idx, 
      {"Item", "Store"}, 
      {
        {
          "A", 
          each 
            let
              a = _, 
              b = List.Skip(
                List.Generate(
                  () => [x = 0, y = a[Stock IN]{0}], 
                  each [x] <= Table.RowCount(a), 
                  each [
                    x = [x] + 1, 
                    y = 
                      if [x] = 0 then
                        [y] - a[Stock OUT]{[x]}
                      else
                        [y] + a[Stock IN]{[x]} - a[Stock OUT]{[x]}, 
                    z = {[y], y}
                  ], 
                  each [z]?
                )
              ), 
              c = List.Transform({0 .. Table.RowCount(a) - 1}, each Table.ToRows(a){_} & b{_}), 
              d = Table.FromRows(c, Table.ColumnNames(a) & {"Start Stock", "End Stock"})
            in
              d
        }
      }
    )[A]
  ), 
  Sol = Table.RemoveColumns(Table.Sort(Group, {{"Idx", Order.Ascending}}), {"Idx"})
in
  Sol
Power Query solution 6 for Calculate Stock Start End, proposed by Hussein SATOUR: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Item", "Store"}, 
    {
      {
        "All", 
        each _, 
        type table [Month = text, Item = text, Store = text, Stock IN = number, Stock OUT = number]
      }
    }
  ), 
  AddCust = Table.AddColumn(
    Group, 
    "Custom", 
    each [
      a = Table.AddIndexColumn([All], "Ind", 1), 
      b = Table.AddColumn(
        a, 
        "End", 
        each List.Sum(List.FirstN(a[Stock IN], [Ind])) - List.Sum(List.FirstN(a[Stock OUT], [Ind]))
      ), 
      c = Table.AddColumn(b, "Start", each [End] + [Stock OUT] - [Stock IN])
    ][c]
  ), 
  Expand = Table.ExpandTableColumn(
    AddCust, 
    "Custom", 
    {"End", "Month", "Start", "Stock IN", "Stock OUT"}, 
    {"End", "Month", "Start", "Stock IN", "Stock OUT"}
  ), 
  Removs = Table.RemoveColumns(Expand, {"All"})
in
  Removs
Power Query solution 7 for Calculate Stock Start End, proposed by Eric Laforce: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tData197"]}[Content], 
  Add_Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), 
  Group = Table.Group(
    Add_Index, 
    {"Item", "Store"}, 
    {
      "G", 
      each List.Generate(
        () => [i = 0, r = _{i}, ss = r[Stock IN], es = ss - r[Stock OUT]], 
        (x) => x[i] < Table.RowCount(_), 
        (x) => [i = x[i] + 1, r = _{i}, ss = x[es], es = ss + r[Stock IN] + r[Stock OUT]], 
        each [r] & [#"Start Stock" = [ss], #"End Stock" = [es]]
      )
    }
  ), 
  Combine = Table.FromRecords(List.Combine(Group[G])), 
  SortBack = Table.RemoveColumns(Table.Sort(Combine, "Index"), "Index")
in
  SortBack
Power Query solution 8 for Calculate Stock Start End, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده: 
let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  A = Table.Group(
    S, 
    {"Item", "Store"}, 
    {
      {
        "Tbl", 
        each _, 
        type table [Month = text, Item = text, Store = text, Stock IN = number, Stock OUT = number]
      }
    }
  ), 
  MF = (T) =>
    let
      B = Table.AddIndexColumn(T, "I", 0, 1, Int64.Type), 
      C = Table.AddColumn(B, "S", each [Stock IN] - [Stock OUT], type number), 
      D = Table.AddColumn(
        C, 
        "End Stock", 
        each if [I] = 0 then [S] else List.Sum(List.FirstN(C[S], [I] + 1))
      ), 
      E = Table.AddColumn(
        D, 
        "Start Stock", 
        each if [I] = 0 then [Stock IN] else D[End Stock]{[I] - 1}
      ), 
      F = Table.SelectColumns(
        E, 
        {"Month", "Item", "Store", "Stock IN", "Stock OUT", "Start Stock", "End Stock"}
      )
    in
      F, 
  B = Table.AddColumn(A, "MF", each MF([Tbl])), 
  C = Table.SelectColumns(B, {"MF"}), 
  D = Table.ExpandTableColumn(
    C, 
    "MF", 
    {"Month", "Item", "Store", "Stock IN", "Stock OUT", "Start Stock", "End Stock"}, 
    {"Month", "Item", "Store", "Stock IN", "Stock OUT", "Start Stock", "End Stock"}
  ), 
  E = Table.AddColumn(D, "IM", each List.PositionOf(List.Distinct(S[Month]), [Month])), 
  F = Table.Sort(
    E, 
    {{"IM", Order.Ascending}, {"Item", Order.Ascending}, {"Store", Order.Ascending}}
  ), 
  G = Table.RemoveColumns(F, {"IM"})
in
  G

Solving the challenge of Calculate Stock Start End with Excel

Excel solution 1 for Calculate Stock Start End, proposed by Bo Rydobon 🇹🇭: 
=LET(l,
    LAMBDA(y,
    MAP(E2:E21,
    LAMBDA(v,
    LET(a,
    B2:v,
    r,
    ROWS(
        a
    ),
    b,
    BYROW(
        TAKE(
            a,
            ,
            2
        ),
        CONCAT
    ),
    x,
    TAKE(
        b,
        -1
    ),
    
IF(IF(
    y,
    XMATCH(
        x,
        b
    )=r,
    0
),
    INDEX(
        a,
        r,
        3
    ),
    SUM(DROP(DROP(
            a,
            ,
            2
        )*{1,
    -1}*(b=x),
    -y))))))),
    HSTACK(
        A2:E21,
        l(
            1
        ),
        l(
            0
        )
    ))
Excel solution 2 for Calculate Stock Start End, proposed by محمد حلمي: 
=XLOOKUP(
    B2&C2,
    $B$1:B1&$C$1:C1,
    $Q$1:Q1,
    D2,
    ,
    -1
)
Q2:
=IF(
    P2<>D2,
    P2-E2+D2,
    D2-E2
)
Excel solution 3 for Calculate Stock Start End, proposed by محمد حلمي: 
=REDUCE(M1:N1,
    D2:D21,
    LAMBDA(a,
    v,
    LET(
s,
    XLOOKUP(
        CONCAT(
            TAKE(
                B2:v,
                -1,
                2
            )
        ),
        
        B1:OFFSET(
            v,
            -1,
            -2
        )&C1:OFFSET(
            v,
            -1,
            -1
        ),
        TAKE(
            a,
            ,
            -1
        ),
        v,
        ,
        -1
    ),
    
VSTACK(a,
    HSTACK(s,
    (s<>v)*s+v-OFFSET(
        v,
        ,
        1
    ))))))
Excel solution 4 for Calculate Stock Start End, proposed by Julian Poeltl: 
=LET(T,
    A1:E21,
    TT,
    DROP(
        T,
        1
    ),
    In,
    CHOOSECOLS(
        TT,
        4
    ),
    D,
    In-TAKE(
        TT,
        ,
        -1
    ),
    M,
    MONTH(
        1&TAKE(
            TT,
            ,
            1
        )
    ),
    I,
    CHOOSECOLS(
        TT,
        2
    ),
    S,
    CHOOSECOLS(
        TT,
        3
    ),
    ES,
    MAP(M,
    I&S,
    LAMBDA(A,
    B,
    SUM(FILTER(D,
    (M<=A)*(I&S=B))))),
    SS,
    MAP(M,
    I&S,
    LAMBDA(A,
    B,
    IFERROR(SUM(FILTER(D,
    (M

Solving the challenge of Calculate Stock Start End with Python

Python solution 1 for Calculate Stock Start End, proposed by Konrad Gryczan, PhD: 
import pandas as pd
path = "PQ_Challenge_197.xlsx"
input = pd.read_excel(path, usecols="A:E")
test  = pd.read_excel(path, usecols="H:N")
test.columns = test.columns.str.replace(".1", "")
input["Month"] = pd.to_datetime(input["Month"], format="%b").dt.month
input = input.sort_values(["Store", "Item", "Month"]).reset_index(drop=True)
input["Row"] = input.groupby(["Store", "Item"]).cumcount()+1
for i in range(len(input)):
 if input.loc[i, "Row"] == 1:
 input.loc[i, "Start Stock"] = input.loc[i, "Stock IN"]
 input.loc[i, "End Stock"] = input.loc[i, "Stock IN"] - input.loc[i, "Stock OUT"]
 else:
 input.loc[i, "Start Stock"] = input.loc[i-1, "End Stock"]
 input.loc[i, "End Stock"] = input.loc[i, "Start Stock"] - input.loc[i, "Stock OUT"] + input.loc[i, "Stock IN"]
input["Month"] = pd.to_datetime(input["Month"], format="%m").dt.strftime("%b")
input["Start Stock"] = input["Start Stock"].astype("int64")
input["End Stock"] = input["End Stock"].astype("int64")
result = test.merge(input, on=["Store", "Item", "Month", "Stock IN"], how="left", suffixes=("_test", ""))
result = result[["Month","Item","Store",   "Stock IN", "Stock OUT", "Start Stock", "End Stock"]]
print(result.equals(test))

Solving the challenge of Calculate Stock Start End with R

R solution 1 for Calculate Stock Start End, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_197.xlsx"
input = read_xlsx(path, range = "A1:E21")
test = read_xlsx(path, range = "H1:N21")
result <- input %>%
 group_by(Item, Store) %>%
 mutate(data = accumulate(`Stock IN` - `Stock OUT`, `+`, .init = 0)[-1],
 `Start Stock` = lag(data, default = first(`Stock IN`)),
 `End Stock` = data) %>%
 ungroup() %>%
 select(-data) 
identical(result, test)
#> [1] TRUE
R solution 2 for Calculate Stock Start End, proposed by Anil Kumar Goyal: 
df <- read_excel("PQ/PQ_Challenge_197.xlsx", range = cell_cols(LETTERS[1:5])) %>% 
 janitor::clean_names()
df %>% 
 mutate(data = 0,
 data = lag(data, default = first(stock_in)),
 data = accumulate2(stock_in - stock_out, data, 
 .f = ~list(start = ..1[["end"]] + ..3, 
 end = ..1[["end"]] + ..2),
 .init = list(start = NA, end = 0))[-1],
 .by = c(item, store)) %>% 
 unnest_wider(data)

Solving the challenge of Calculate Stock Start End with DAX

DAX solution 1 for Calculate Stock Start End, proposed by Zoran Milokanović: 
EVALUATE
ADDCOLUMNS(Input,
"Start Stock",
 COALESCE(CALCULATE(SUMX(Input, Input[Stock IN] - Input[Stock OUT]), ALLEXCEPT(Input, Input[Item], Input[Store]), 
 FIND(Input[Month], "JanFebMarAprMayJunJulAugSepOctNovDec") < FIND(EARLIER(Input[Month]), "JanFebMarAprMayJunJulAugSepOctNovDec")), Input[Stock IN]),
"End Stock",
 CALCULATE(SUMX(Input, Input[Stock IN] - Input[Stock OUT]), ALLEXCEPT(Input, Input[Item], Input[Store]), 
 FIND(Input[Month], "JanFebMarAprMayJunJulAugSepOctNovDec") <= FIND(EARLIER(Input[Month]), "JanFebMarAprMayJunJulAugSepOctNovDec"))
)

&&&

Leave a Reply