Accumulation in collection (and calculation between colums in collection)

(0) Share

Report

Posted on by jlke-as

130

I have a collection with these purchase figures (sorted in date order)...

"Remain" is a figure collected in another table, showing the QTY on stock...

I would like to accumulate the data in QTY per Material... (in a new column called "CUM_QTY")

Afterwards I would like a to add a new column called "Cur_remail" in which I would like to make a calculation, showing how many QTY of the given purchase that is still on stock...

(in excel my formular in H2 is "=MAX(IF(D2>G2;C2;D2-G2+C2);0)", but don't know how to apply it to Power Apps")

Can anyone help me to get these 2 calculations into my gallery/collection?

wkr Jørgen

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

v-bofeng-msft on at

Like (0)

Report
Re: Accumulation in collection (and calculation between colums in collection)

Hi @jlke-as ,

I assume there is a collection:

ClearCollect( TheCollection, {Material:"A",Date:Date(2023,5,21),QTY:10,Remain:15}, {Material:"B",Date:Date(2023,5,20),QTY:10,Remain:12}, {Material:"C",Date:Date(2023,5,19),QTY:20,Remain:25}, {Material:"A",Date:Date(2023,5,18),QTY:30,Remain:15}, {Material:"C",Date:Date(2023,5,17),QTY:50,Remain:25}, {Material:"A",Date:Date(2023,5,16),QTY:20,Remain:15}, {Material:"B",Date:Date(2023,5,15),QTY:20,Remain:12} );

You could try:

AddColumns( AddColumns( TheCollection As A, "CUM_QTY", A[@QTY]+Sum(Filter(TheCollection,Material=A[@Material] && Date>A[@Date]),QTY) ), "Cur_remain", Max(If(Remain>CUM_QTY,QTY,Remain-CUM_QTY+QTY),0) )

Best Regards,

Bof

Was this reply helpful? Yes No
jlke-as 130 on at

Like (0)

Report

Re: Accumulation in collection (and calculation between colums in collection)

Hi Bof...
Perfect... You have saved my day... 😉
Could you please explain the "As A" in you solution?
wkr Jørgen

Was this reply helpful? Yes No