previous balance by descending stock

[Ribeiro]

Below I have two related tables
sales and stock,

I need to create from the stock balance a descending previous balance.

Order by

branch,Product,Date,Ordem

sales table

branch	Product	Date	Seq	Ordem	Entry	Exit
10	100	10/1/21 14:51	3000	100	1	0
10	100	11/1/21 15:51	3001	101	0	2
10	100	12/1/21 16:51	3002	102	5	0
20	200	13/1/21 10:51	4003	1003	0	4
20	200	14/1/21 11:51	4004	1004	3	0
20	200	15/1/21 12:51	4005	1005	0	2

Table Stock
branch	Product	Final storage
10	100	8
20	200	7

Desired result:

branch	Product	Date	Seq	Ordem	Previous balance	Entry	Exit	Inventory balance
10	100	10/1/21 14:51	3000	100	10	1	0	11
10	100	11/1/21 15:51	3001	101	11	0	2	13
10	100	12/1/21 16:51	3002	102	13	5	0	8
20	200	13/1/21 10:51	4003	1003	4	0	4	8
20	200	14/1/21 11:51	4004	1004	8	3	0	5
20	200	15/1/21 12:51	4005	1005	5	0	2	7

[vinieme12 · Accepted solution]

temp_fact:
Load * Inline [
branch,Product,Date,Seq,Ordem,Entry,Exit
10,100,10/1/21 14:51,3000,100,1,0
10,100,11/1/21 15:51,3001,101,0,2
10,100,12/1/21 16:51,3002,102,5,0
20,200,13/1/21 10:51,4003,1003,0,4
20,200,14/1/21 11:51,4004,1004,3,0
20,200,15/1/21 12:51,4005,1005,0,2
];

Left Join(temp_fact)
Load * Inline [
branch,Product,FinalStorage
10,100,8
20,200,7
];

Fact:
Load *
,if(branch=Peek('branch') and Product = peek('Product')
, Peek('PreviousBalance')+Entry-Exit
, FinalStorage+Entry-Exit) as PreviousBalance

,if(branch=Peek('branch') and Product = peek('Product')
, Peek('PreviousBalance')
, FinalStorage) as InventoryBalance

Resident temp_fact
Order by branch,Product,Date Desc;
Drop table temp_fact;
exit Script;