The Question’s table outlines warehouse transactions with records for Initial value (warehouse product count), Add (purchased and incoming products), and Reduce (sold and outgoing products). The result table shows the average daily inventory levels per product in each month. For instance, product C’s inventory was 20 from 01.01.2024 to 15.01.2024, then decreased to 10 for the rest of the month. So, over 14 days, the inventory was 20, and for 17 days, it was 10, resulting in an average of 14.5.
📌 Challenge Details and Links
Challenge Number: 8
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Calculate Average Inventory Level! with Power Query
Power Query solution 1 for Calculate Average Inventory Level!, proposed by Omid Motamedisedeh:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Merge = Table.CombineColumns(
Table.TransformColumnTypes(S, {{"Date", type date}}),
{"Type", "Quantity"},
each (if _{0} = "Reduce" then - 1 else 1) * _{1},
"V"
),
G = Table.Group(Merge, {"Date", "Product"}, {{"V", each List.Sum([V]), type number}}),
New = Table.Distinct(
Table.Combine(
{G, Table.TransformColumns(G, {{"V", each 0}, {"Date", each Date.StartOfMonth(_)}})}
),
{"Date", "Product"}
),
C1 = Table.AddColumn(
New,
"Custom2",
each [
a = List.Min(Table.SelectRows(New, (x) => x[Date] > [Date] and x[Product] = [Product])[Date]),
b = (if a = null then Date.EndOfMonth([Date]) + #duration(1, 0, 0, 0) else a)
][b]
- [Date]
),
C2 = Table.TransformColumns(
Table.AddColumn(
C1,
"Custom",
each List.Sum(Table.SelectRows(C1, (x) => x[Date] <= [Date] and x[Product] = [Product])[V])
* [Custom2]
),
{{"Date", Date.Month}}
),
Group = Table.Group(
C2,
{"Date", "Product"},
{{"C1", each List.Sum([Custom]) / List.Sum([Custom2])}}
),
#"Pivoted Column" = Table.Pivot(Group, List.Distinct(Group[Product]), "Product", "C1", List.Sum)
in
#"Pivoted Column"Power Query solution 2 for Calculate Average Inventory Level!, proposed by Omid Motamedisedeh:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Merge = Table.CombineColumns(
Table.TransformColumnTypes(S, {{"Date", type date}}),
{"Type", "Quantity"},
each (if _{0} = "Reduce" then - 1 else 1) * _{1},
"V"
),
Dat_list = [
a = Merge[Date],
b = List.Dates(
List.Min(a),
Number.From(Date.EndOfMonth(List.Max(a)) - List.Min(a)) + 1,
#duration(1, 0, 0, 0)
),
c = List.Transform(
b,
each [
date = Date.Month(_),
x = (
List.Transform(
List.Distinct(S[Product]),
(x) => [
product = x,
valu = List.Sum(Table.SelectRows(Merge, (y) => y[Date] <= _ and y[Product] = x)[V])
]
)
)
]
)
][c],
CtoTable = Table.ExpandRecordColumn(
Table.ExpandListColumn(
Table.ExpandRecordColumn(
Table.FromList(Dat_list, Splitter.SplitByNothing()),
"Column1",
{"date", "x"},
{"date", "x"}
),
"x"
),
"x",
{"product", "valu"},
{"product", "valu"}
),
Group = Table.Group(CtoTable, {"date", "product"}, {{"Count", each List.Average([valu])}}),
Pivoted = Table.Pivot(Group, List.Distinct(Group[product]), "product", "Count", List.Sum)
in
PivotedPower Query solution 3 for Calculate Average Inventory Level!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
S2 = Table.Group(S, {"Product"}, {{"Tbl", each _, type table [Date=nullable datetime, Product=nullable text, Type=nullable text, Quantity=nullable number]}}),
Me=(TBL)=>
let
I = Table.AddIndexColumn(TBL, "I", 1, 1, Int64.Type),
A2 = Table.AddColumn(I, "FQTY", each if [Type]="Reduce" then -[Quantity] else [Quantity]),
A3 = Table.AddColumn(A2, "Qty", each List.Sum(List.FirstN(A2[FQTY],[I]))),
C2 = Table.TransformColumnTypes(A3,{{"Date", type date}}),
LD=Table.FromList({Number.From(Date.From(Date.StartOfMonth (List.Min(C2[Date]))))..Number.From(Date.From(Date.EndOfMonth (List.Max(C2[Date]))))},Splitter.SplitByNothing(),{"Dl"}),
C = Table.TransformColumnTypes(LD,{{"Dl", type date}}),
C3 = Table.NestedJoin(C,{"Dl"},C2,{"Date"},"In",JoinKind.LeftOuter),
E = Table.ExpandTableColumn(C3, "In", {"Qty"}, {"In.Qty"}),
F = Table.FillDown(E,{"In.Qty"}),
E2 = Table.TransformColumns(F,{{"Dl", Date.Month, Int64.Type}}),
G = Table.Group(E2, {"Dl"}, {{"Ave", each List.Average([In.Qty]), type number}})
in
G,
Solving the challenge of Calculate Average Inventory Level! with Excel
Excel solution 1 for Calculate Average Inventory Level!, proposed by Bo Rydobon 🇹🇭:
=LET(p,C3:C19,d,B3:B19,q,MAP(p,LAMBDA(a,SUM({1,-1}*SUMIFS(INDEX(E3:a,,3),a:C3,a,INDEX(E3:a,,2),{"<R","R*"})))),f,MIN(d),e,SEQUENCE(EOMONTH(MAX(d),0)+1-f,,f),<br>u,TOROW(UNIQUE(p)),m,TEXT(e,"mm"),r,TOCOL(XLOOKUP(u&e,p&d,q,,-1,-1)),n,UNIQUE(m),<br>h,TOCOL(IFNA(m,u))&TOCOL(IFNA(u,e)),x,MAP(n&u,LAMBDA(v,ROUND(AVERAGE(FILTER(r,h=v)),1))),<br>VSTACK(HSTACK("Month",u),HSTACK(--n,x)))Excel solution 2 for Calculate Average Inventory Level!, proposed by Bo Rydobon 🇹🇭:
=LET(p,C3:C19,d,B3:B19,q,MAP(p,LAMBDA(a,SUM({1,-1}*SUMIFS(INDEX(E3:a,,3),a:C3,a,INDEX(E3:a,,2),{"<R","R*"})))),f,MIN(d),e,SEQUENCE(EOMONTH(MAX(d),0)+1-f,,f),<br>u,TOROW(UNIQUE(p)),PIVOTBY(TOCOL(IFNA(MONTH(e),u)),TOCOL(IFNA(u,e)),TOCOL(XLOOKUP(u&e,p&d,q,,-1,-1)),AVERAGE,,0,,0))Excel solution 3 for Calculate Average Inventory Level!, proposed by محمد حلمي:
=LET(
p,
B3:E19,
m,
MONTH(
B3:B19
),k,
C3:C19,
u,
UNIQUE(
k
),
REDUCE(HSTACK(
"Month",
TOROW(
u
)
),
UNIQUE(
m
),LAMBDA(c,
v,
VSTACK(c,
HSTACK(v,
DROP(REDUCE(0,
u,
LAMBDA(a,
d,
HSTACK(a,
LET(j,
k=d,
w,
FILTER(p,
j*(m=v)),rr,
LAMBDA(
qq,
ee,
SCAN(
0,
IF(
LEFT(
INDEX(
qq,
,
3
)
)="R",
-ee,
ee
),
LAMBDA(
a,
d,
a+d
)
)
),q,
FILTER(p,
j*(m=v-1)),
vv,
TAKE(
q,
,
-1
),
xx,
IF(
v=1,
w, VSTACK(
HSTACK(
EOMONTH(
@w,
-1
)+1,
d,
"",
TAKE(
rr(
q,
vv
),
-1
)
),
w
)
),v,
TAKE(
xx,
,
1
),
e,
EOMONTH(
@xx,
0
),i,
DROP(
VSTACK(
v,
e+1
),
1
)-v,
ROUND(SUM((i/DAY(
e
))*rr(
xx,
TAKE(
xx,
,
-1
)
)),
1))))),
,
1))))))Excel solution 4 for Calculate Average Inventory Level!, proposed by Kris Jaganah:
=LET(
a,
B3:B19,
b,
C3:C19,
c,
E3:E19,
d,
IF(
D3:D19="Reduce",
-c,
c
),
e,
SORT(
TOCOL(
TOROW(
UNIQUE(
b
)
)&SEQUENCE(
EOMONTH(
MAX(
a
),
0
)-MIN(
a
),
,
MIN(
a
)
)
)
),
f,
--RIGHT(
e,
5
),
g,
XLOOKUP(
e,
b&a,
d,
0
),
h,
SCAN(
0,
IF(
f=45292,
g+0.1,
g
),
LAMBDA(
x,
y,
IF(
INT(
y
)=y,
x+y,
INT(
y
)
)
)
),
PIVOTBY(
MONTH(
f
),
LEFT(
e
),
h,
AVERAGE,
0,
0,
,
0
)
)Excel solution 5 for Calculate Average Inventory Level!, proposed by John Jairo Vergara Domínguez:
=LET(d,
B3:B19,
p,
C3:C19,
s,
SEQUENCE(
1+EOMONTH(
MAX(
d
),
0
)-@+d,
,
@+d
),
u,
TOROW(
UNIQUE(
p
)
),
e,
TOCOL(
IF(
s,
u
),
,
1
),
f,
TOCOL(s*(u>""),
,
1),
PIVOTBY(
MONTH(
f
),
e,
XLOOKUP(
e&f,
p&d,
MMULT(
SUMIFS(
E3:E19,
D3:D19,
{"<>R*",
"R*"},
p,
p,
d,
"<="&d
),
{1;-1}
),
,
-1
),
AVERAGE,
,
0,
,
0
))