convert dax to power query m

[Yingjing]

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!

[AlienSx]

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

[Yingjing]

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

[AlienSx · Accepted solution]

@Yingjing 

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

[Yingjing]

I'm grateful that your solution works, thanks a lot!😁