There is some clients when I implement this and i pass with the same challenge, you could do it use a:
Remaing = "invoice amount - sum( Receipt Amount ) group by invoice document)"
However it not the best solution, this kind of information has to be calculated in the ETL process.
+1 to Cesar ... Balances are inventory-style facts and need to be stored in the appropriate fact structure. Once you have it right, then you can show increase/decrease in balance over time AND also add value by having an aging balance fact built from the base balance fact.
Thanks for your response. Unfortunately our clients approach is to write view out of tables from the trabsactional data and pull the view in BI to create reports. Now I do not have any option in implementing this in the ETL. Do you think I can write this in the view ? Or from the reporting end ?
OK, if you dont have any other solutions, you should use analytics function LAG/LEAD to calculate your report.