Open Amounts in the past

[Anonymous]

Hi there,

I am looking for a solution for the following:

In Summary: Based on invoices (accounts receivable), what were the open amounts, by month in the past 12 months.

Situation: Invoices that are open for several months. There are two ways of crediting an amount: payments and reconciliations. In essence this means there are two seperate dates and amounts that need to be taken into consideration.

Exceptions: Payment date &/or reconciliation date can be empty

Example: Current date is December 7th 2023. An invoice was booked in January 2023 for $10.000. In March a reconciliation of $2.000 is performed. On June 2023, a payment is performed of $7.000 remaining in an open balance of $1.000 bt the end of june.

Specifications: Open amounts by the end of the month will suffice. So if an invoice is paid in the middle of the month, by the end of that specific month, the open amount displayed can be the amount that remains open.


Required result:
Based on the example above: open amount
- january: $10.000,-, 

- february: $10.000,-, 

- march: $8.000,-

- april: $8.000,-

- may: $8.000,-

- Jun: $1.000,-

- July $1.000,-

- Aug  $1.000,-

-Sep  $1.000,-

- Oct  $1.000,-

- Nov  $1.000,-

- Dec  $1.000,-

above given calculation should ofcourse be performed on multiple invoices.

As an example, a couple of invoices with different kinds of scenario's:

[v-junyant-msft]

Hi @Anonymous ,

Since I don't know what your raw data looks like, I created a test dataset myself:

Considering that the order of the months in your raw data may not be as regular as the one I tested, to facilitate indexing, I added an index column to Power Query:

Then I use the DAX below to create a new column to calculate:

Open Amounts = 
VAR Total_Invoice = CALCULATE(SUM('Table'[Invoice]), FILTER('Table', 'Table'[Index] <= EARLIER('Table'[Index])))
VAR Total_Payment = CALCULATE(SUM('Table'[Payment]), FILTER('Table', 'Table'[Index] <= EARLIER('Table'[Index])))
VAR Total_Reconciliation = CALCULATE(SUM('Table'[Reconciliation]), FILTER('Table', 'Table'[Index] <= EARLIER('Table'[Index])))
RETURN
Total_Invoice - Total_Payment - Total_Reconciliation

The results are as follows:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi there, Thank you so much for your help on this.
The data consists however of not one, but three relevant date-columns (as illustrated in my printscreen). So allocating an index would be usable based on one date column, but taking the other two into account is still a puzzle to me.

Would you be able to simulate it based on my example (with three dats and different amounts (partial payment etc).?

Thank you

[Anonymous]

Please help me with this 🙂