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

Reply
Capsule
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:

 

Capsule_0-1701956385656.png

 

 

 

3 REPLIES 3
v-junyant-msft
Community Support
Community Support

Hi @Capsule ,

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

vjunyantmsft_0-1702358280178.png

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:

vjunyantmsft_1-1702358366489.png

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:

vjunyantmsft_2-1702358482328.png


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.

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

Capsule
New Member

Please help me with this 🙂

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors