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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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 @Anonymous ,

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.

Anonymous
Not applicable

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
Not applicable

Please help me with this 🙂

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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