I’m working on a workflow and need to calculate the number of outstanding invoices prior to the creation date of a new invoice for each customer. Specifically, I want to compare the creation date of the current invoice to the payment dates of all previous invoices for the same customer. If the creation date is before the payment date of any previous invoice, it should be counted as outstanding.
For example, given the following data, I'd like to obtain the last column in the table below:
| Customer ID | Invoice ID | Invoice creation | Payment date | OutstandingInvoices |
| 1 | 1 | 2024-01-28 | 2024-02-09 | null |
| 1 | 2 | 2024-01-30 | 2024-02-10 | 1 |
| 1 | 3 | 2024-02-16 | 2024-05-05 | 0 |
| 1 | 4 | 2024-03-09 | 2024-05-10 | 1 |
| 1 | 5 | 2024-04-30 | 2024-05-02 | 2 |
| 1 | 6 | 2024-05-06 | 2024-05-30 | 1 |
For invoice 1, there are no previous invoices, so the outstanding count is null. For invoice 2, it was created before the payment of invoice 1, so the count is 1. For invoice 3, it was created after the payment of invoices 1 and 2, so the count is 0, and so on.
How can I achieve this in Alteryx? My first approach was to use a multi-row formula tool, but I would like to have more flexibility on the number of rows that will be used for the calculation, as they vary depending on the number of invoices issue to different customers.
