Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Open Amounts in the past

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:

3 REPLIES 3
Community Support

Hi @Capsule ,

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.

New Member

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

New Member

Please help me with this 🙂

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors