Solved

# Tracking Outstanding Invoices over Time

• 2 replies
• 66 views

• Newly Minted Pigmenteer
• 2 replies

Hi!

I’d like to create a metric to keep track of the number of outstanding customer invoices over time.

I have an import from the ERP system with the status of every issued invoice, and the following columns:

• Customer Number
• Invoice Date
• Payment Date

The metric should be of dimension Day, and should return the # of outstanding invoices at any given time.

Excel equivalent:
1. Generated a sequence of days ( → Day metric in Pigment)
2. Use sumproduct formula for the aggregation

``=SUMPRODUCT((Invoice_Date>=Day)*((Payment_Date>Day))+(Payment_Date=0))``

What would be the equivalent formula in Pigment for this?

icon

Best answer by francois 21 March 2023, 09:56

View original

### 2 replies

Userlevel 6
+14

Hi Oyvind! Happy to see you in the community.

Here’s how I’d do it:

• first create a count of customer invoices by day (similar to this topic, using `BY COUNT` as an aggregator)
• then use `CUMULATE` on the Month, starting from the last Month of the calendar.

Using `CUMULATE` from the last month of the calendar will require using the `ON` operator, followed by a ranking formula, like this for example:

In your case, the final formula could look something like this:

`CUMULATE(Invoice[BY COUNT: Invoice.Customer, Invoice.Date], Day ON -RANK(Day.'Start Date'))`

Let me know if you can make it work!

Thanks @francois

Tweaked the proposed solution a bit, using two CUMULATE functions. One to keep track of the total number of issued invoices over time, and a second to subtract the total number of paid invoices over time, taking the delta of the two to track the number of unpaid invoices.