Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
dennysetiawan
Partner - Contributor III
Partner - Contributor III

Determine beginning balance, mutation, and ending balance from daily to monthly transaction

Dear Expert,

I have a daily transaction data like below:

TRX_DATETRX_TYPEAMOUNTBALANCE_BEFOREBALANCE_AFTER
27-Oct-2020Purchase100005000040000
7-Nov-2020Purchase70004000033000
7-Nov-2020Top Up120003300045000
7-Nov-2020Purchase85004500036500
20-Nov-2020Purchase75003650029000
21-Nov-2020Top Up150002900044000
22-Nov-2020Purchase125004400031500
6-Dec-2020Purchase115003150020000
25-Dec-2020Purchase18500200001500

 

From that, I want to create a table that contains summary per month in which there is a beginning balance, mutation based on trx_type, and ending balance. The formulas are as follows:

 

Beginning Balance = Ending Balance in last month

Top Up = Total Amount with TRX_TYPE = Top Up

Purchase = Total Amount with TRX_TYPE = Purchase

Ending Balance = Beginning Balance + Top Up - Purchase

 

My expectation should be like this:

TRX_MONTHBEGGINING_BALANCETOP UPPURCHASEENDING_BALANCE
Oct-205000001000040000
Nov-2040000270003550031500
Dec-20315000300001500

 

Do you have any solution about this? Thank you for all the advice.

Labels (4)
2 Replies
dennysetiawan
Partner - Contributor III
Partner - Contributor III
Author

Dear Expert,

Any suggestion about this case?

dennysetiawan
Partner - Contributor III
Partner - Contributor III
Author

I'm still looking for a solution about this case.