Running total for active accounts by month since 2020

[nickrosen28]

Hi all!

I have a relatively simple task that I have not been able to solve.

I have a list of accounts, start date and end date:

I am in need of a total by month of all active accounts since 2020.

If the account is labeled active in the end date column it means from their start date up to today they are an active account.

If someone start date is 09/2023 and end date is 07/2024 - I need them included in the total for September all the way through the month of their end date, and then them removed from the total from any month thereafter

Desired output would be ex: 08/2022 - 15

                                             09/2022 - 19 etc..

Any help would be greatly appreciated!

Thanks so much!

[Carolyn · Accepted solution]

Give this a try! What I did was

1. Formula Tool to change an End Date of "Active" or of a later month to today's date - you might want to tweak that as appropriate
   1. I excluded next month b/c what was happening was I was seeing only 1 Active account for December, which isn't true, it's just running into my "Active" -> today logic
2. Filter to exclude where the Start Date was null, b/c I didn't know what to do with that one :) 
3. Multi-Field Formula Tool to convert the Start and End Dates to actual Dates
4. Then the Generate Rows Tool - this starts with the Start Date and does a DateTimeAdd of 1 month until it hits the End Date (which was why "Active" was changed in Step #1)
5. Then a Summarize Tool to count the number of Active Accounts in each month
6. Edit: And a Multi-Field Formula Tool thrown on the end to change the format to "mm/yyyy"