rolling user balance

dj_fid

hi! currently we face an issue with company spendings, and we wanted to calculate rolling balances per company employee (UUID)

Goal: Calculate a monthly rolling balance for each user_uuid, where:

Starting in January, spendings are subtracted from top-ups.
The remaining balance at the end of each month carries over to the next month as the starting balance.

i have tried doing following:
Monthly Balance = [top up amount] - COALESCE([Sum_account_amount], 0)
Rolling Balance = IF [Row-1: user_uuid] = [user_uuid] THEN [Row-1: Rolling Balance] + [Monthly Balance] ELSE [Monthly Balance]
however i get messed up balances always...
thank you in advance

sum of deposits.csv

sum of spendings.csv

Qiu

@dj_fid 
If my understanding is correct.

The Group By feature in Multi-row Formula is designed for your case.

We first do a join of two data with UUID and Month, 

Data from J anchor: The UUID is having Top-up and Spending for that month

Data from L anchor: The UUID is only having Top-up for that month
Data from R anchor: The UUID is only having Spending for that month

then we union the 3 data streams back together, we can get the UUID monthly Top-ups and Spendings.

The rest will be easy.

1030-dj_fid.yxzp