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

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.

AR Balance Example.xlsx

Transformation

Accepted answers

KaneG

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

Acc Balance for Months.yxmd

All comments

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

Hi Alex,

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

Kane

Acc Balance for Months.yxmd

RPM

Thank you. Very helpful.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280