Running Total for A/R balances for months with no activity

[alex]

I need to have the running total of a customer's activity for all months with a balance even for months with no activity.  When I use the running total tool, I only get running totals for months with transactions.  Additionally, there should be no balance in months prior to a customers first date of activity and no balance in months after they have reached zero and have no activity in that month.  I've attached a small file to illustrate.

[MarqueeCrew]

If nobody solves this for you, I will do it in the morning.

Create a set of all month end dates.
Add a field with a value of "join me"
Create a unique set of account numbers
Add a field with a value of "join me"
Join these two sets of data on that field.
Add a null field for balance.
Union this with your real data
Sort by acct, date and balance
Create a multi row formula grouped by account number
Fill in gaps
Filter out null values

That's my approach.

[KaneG · Accepted solution]

Hi Alex,

The attached module will show you how to do this. Essentially I have used the generate rows tool to create a row for each month and then filled in the details using a join and multi-row. This does not account for Opening and closing dates but if you Join a table with columns Customer ID, Open Date, Close Date on Customer ID, then you can use a filter tool to remove records or a formula tool to null() records.

You should be able to play with this to get it as Dynamic as you would like.

Kane

[RPM]

Thank you. Very helpful.