Request for Power BI Aging Report with Historical Snapshot

[edipgashi]

Hello EDNA community,

I’m looking for a solution in Power BI to build an Aging of Receivables report that can show balances as they were on a specific date selected in a slicer. For example, if I choose a date from three months ago, I should see the balance, overdue, not due, and aging breakdown as they were at that time.

I’ve been searching the forum, as well as with AI, but it’s a problem to find the solution. I need it in Power BI since I need to move in time.

In the table visual, I need the following columns:

Balance – Total outstanding amount
Over Due – Portion of the balance that is past due
Not Due – Portion of the balance that is not yet due
0–30 days – Overdue amount aged 0–30 days
31–60 days – Overdue amount aged 31–60 days
61–90 days – Overdue amount aged 61–90 days
91–180 days – Overdue amount aged 91–180 days
181–365 days – Overdue amount aged 181–365 days
+365 days – Overdue amount aged more than 365 days

Important logic details:

• Payments come in bulk and are not tied to specific invoice numbers.
• Needs to apply FIFO logic – credits should be applied starting from the earliest posting date, reducing debits accordingly.
• Once credits are exhausted, the remaining debits should be calculated into the Over Due and Not Due amounts.
• Over Due amounts should then be split into the aging buckets above, based on how old the debt is.

Additional condition that needs to apply:

• Every customer has an Initial Deposit value that is allowed to offset the balance (meaning the balance can be negative).

• This deposit should be subtracted from all calculations. For example, if the Over Due is 350,000 and the Initial Deposit is 100,000, then the displayed Over Due should be 250,000.

• The same logic should also apply to the aging buckets.

Attached is the .pbix and .xlsx files for reference.

Any suggestions or example measures/queries for implementing this logic will be highly appreciated or if you can write the dax on pbix and send to me I will be highly highly appreciated.

Thank you in advance!
Edip
Customer Ledger _T-2005.xlsx (52.5 KB)
Customer Ledger.pbix (123.1 KB)

[Keith]

Hi @edipgashi

There are examples for Account Recievables in the Showcase section within the EDNA Platform

Resource Center and then Showcase

Please take a look at the examples within the this section. Dex calculations are within the powerbi file that is downloaded that will help you

Can also use the Mentor AI to help you with your problem

I hope this helps.
Keith

I hope this helps.
Keith

[edipgashi]

Hi @Keith, thank you,

I have seen those videos, but the issue for me is that I have payments in bulk, not linked to any specific document or invoice number. This means I need to apply FIFO logic so that credits are allocated starting from the earliest posting date, and once the credits are exhausted, the remaining debits should be classified into Over Due and Not Due, with Over Due further split into aging buckets.

I am facing difficulties in implementing this request because of two main challenges:

1. The dynamic filtering combined with the aged bucket allocation for overdue amounts.
2. Performance issues – some of my current calculations return the error “Visual has exceeded the available resources” because the dataset contains millions of rows.

The final output I need is shown in the image:

Table 1– without applying the Initial Deposit.
Table 2 is the Final that I need – with the Initial Deposit applied as requested (if the balance is within the Initial Deposit value, it should display 0).

[Keith]

Have you tried using Mentor AI tool project to help you? You can create a project on the specification you need. Using the Mentor AI Projects.

Did you download the sample account receivable power bi file under the showcase?

thanks
Keith