Dear Expert,
I have a daily transaction data like below:
| TRX_DATE | TRX_TYPE | AMOUNT | BALANCE_BEFORE | BALANCE_AFTER |
| 27-Oct-2020 | Purchase | 10000 | 50000 | 40000 |
| 7-Nov-2020 | Purchase | 7000 | 40000 | 33000 |
| 7-Nov-2020 | Top Up | 12000 | 33000 | 45000 |
| 7-Nov-2020 | Purchase | 8500 | 45000 | 36500 |
| 20-Nov-2020 | Purchase | 7500 | 36500 | 29000 |
| 21-Nov-2020 | Top Up | 15000 | 29000 | 44000 |
| 22-Nov-2020 | Purchase | 12500 | 44000 | 31500 |
| 6-Dec-2020 | Purchase | 11500 | 31500 | 20000 |
| 25-Dec-2020 | Purchase | 18500 | 20000 | 1500 |
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_MONTH | BEGGINING_BALANCE | TOP UP | PURCHASE | ENDING_BALANCE |
| Oct-20 | 50000 | 0 | 10000 | 40000 |
| Nov-20 | 40000 | 27000 | 35500 | 31500 |
| Dec-20 | 31500 | 0 | 30000 | 1500 |
Do you have any solution about this? Thank you for all the advice.
