Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
https://community.fabric.microsoft.com/t5/Desktop/Inventory-aging-report/m-p/2493851#M887356
hi all,
i got a issue similar to the link above but with power query m.
please help to convert the dax to power query m, thanks!
Solved! Go to Solution.
let
Source = your_table,
a = List.Buffer(Table.ToRecords(Table.Sort(Source, "Index"))),
count = List.Count(a),
provision = List.Sum(Source[Provision]),
gena =
List.Generate(
() =>
[i = 0,
prov_before = provision,
prov_after = List.Min({0, a{i}[Value] + prov_before}),
row = a{i} & [Result = prov_before - prov_after]],
(x) => x[i] < count,
(x) =>
[i = x[i] + 1,
prov_before = x[prov_after],
prov_after = List.Min({0, prov_before + a{i}[Value]}),
row = a{i} & [Result = prov_before - prov_after]],
(x) => x[row]
),
tbl = Table.FromRecords(gena)
in
tbl
Hello, @Yingjing
let
Source = your_table,
fx_fifo = (tbl as table) =>
[a = List.Buffer(Table.ToRecords(Table.Sort(tbl, "Date"))),
count = List.Count(a),
stock_out = List.Sum(tbl[Stock Out]),
gena =
List.Generate(
() =>
[i = 0,
sold = a{i}[Stock Out] = null,
stock_before = stock_out,
stock_after = if sold then List.Max({0, stock_before - a{i}[New Stock]}) else stock_before,
row = a{i} & [Result = if sold then List.Max({0, a{i}[New Stock] - stock_before}) else null]],
(x) => x[i] < count,
(x) =>
[i = x[i] + 1,
sold = a{i}[Stock Out] = null,
stock_before = x[stock_after],
stock_after = if sold then List.Max({0, stock_before - a{i}[New Stock]}) else stock_before,
row = a{i} & [Result = if sold then List.Max({0, a{i}[New Stock] - stock_before}) else null]],
(x) => x[row]
),
res = Table.FromRecords(gena)][res],
group = Table.Group(Source, "Branch", {{"trn", fx_fifo}}),
expand = Table.ExpandTableColumn(group, "trn", {"Date", "New Stock", "Stock Out", "Result"})
in
expand
Hi Alien,
hows the pq m would be if it is for the table below (allocate "provision" according to sequence of "index", but not exceeding "value"). Result is the outcome I want.
index 1 "result"= -1784604 (mqx of "Value")
index 2 "result" = -457394 (-2241997.62+1784604 )
Attribute | Index | Value | Provision | Result |
> 5 years | 1 | 1784603.62 | -2241997.62 | -1784604 |
> 4 - 5 years | 2 | 670995.44 | 0 | -457394 |
> 3 - 4 years | 3 | 577133.78 | 0 | 0 |
> 2 - 3 years | 4 | 544003.51 | 0 | 0 |
> 1 - 2 years | 5 | 3913885.94 | 0 | 0 |
> 6 mths - 1 year | 6 | 9673161.91 | 0 | 0 |
< 6 mths | 7 | 50572704.55 | 0 | 0 |
let
Source = your_table,
a = List.Buffer(Table.ToRecords(Table.Sort(Source, "Index"))),
count = List.Count(a),
provision = List.Sum(Source[Provision]),
gena =
List.Generate(
() =>
[i = 0,
prov_before = provision,
prov_after = List.Min({0, a{i}[Value] + prov_before}),
row = a{i} & [Result = prov_before - prov_after]],
(x) => x[i] < count,
(x) =>
[i = x[i] + 1,
prov_before = x[prov_after],
prov_after = List.Min({0, prov_before + a{i}[Value]}),
row = a{i} & [Result = prov_before - prov_after]],
(x) => x[row]
),
tbl = Table.FromRecords(gena)
in
tbl
I'm grateful that your solution works, thanks a lot!😁
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
70 | |
63 | |
40 | |
28 | |
15 |