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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
NickMorrell
Frequent Visitor

Historical Aged Trial Balance trend graph

Hi everyone,
I have a very functional aged trial balance report that I would like to add a graph to. I want the graph to show the results of an aged trial balance by month - i.e., how much was overdue at the end of each month (and I'll provide column references to help with your answers).

 

I have an invoice table that shows the current balance [CurrentBalance] of each invoice [InvoiceNo], and the issue I have with displaying historical ATB data is that obviously these old invoices are paid off now - their balance shows as zero on this table.

 

I do have a separate transaction table that shows more detailed data though - it has the invoice creation and payments date [TransactionDate], the invoice amount [InvoiceNettValue], and how much of that invoice was paid off at which date by the customer [PostingNettValue]. 

Important Note: we display the amount owed as positive, and the customer's incoming payments as negatives, i.e. "paying it down" until it reaches zero and the invoice is considering complete.

Capture.PNG

 

I have due dates for the invoices as well [Due Date], and each customer has a "terms code" [TermsCode] which details when they've agreed to pay off their invoice; 7 days, the end of the next month, 30 days from the invoice, etc.

 

What I want to build is a table/column/measure that'll essentially run its own aged trial balance for each month period in the transaction tables, and be able to display overdue total only. I'll use this in a graph by months to show whether the ATB is improving (overdue funds at end of month decreasing) or getting worse (overdue funds at end of month increasing). 

I understand that this is something of a colossal undertaking - I consider myself moderately good at Power BI yet I've run up against lots of walls in bringing this idea to fruition. 

 

Attached is an image of what I've been able to generate so far - it illustrates the problem I'm having with Current Balance; March is largest because we're currently in March and thus there are the most unpaid invoices; invoices that are months overdue become less and less likely hence the graph decreases. 

 

Capture.PNG

 

For clarity regarding what's on which table:

Accounts Receivable Invoices:

  • [CurrentBalance] - current balance of invoice, updated regularly from database
  • [InvoiceNo] - unique invoice number identifier
  • [InvoiceDate] - date invoice was created
  • [Due Date] - date invoice is due, generated by [TermsCode]
  • [TermsCode] - specifies number of days during which invoice should be paid off

 

Accounts Receivable Transactions:

  • [InvoiceNo] - same as above table, used to link the two tables
  • [TransactionDate] - date field that contains all movements related to an invoice; creation, customer payments
  • [InvoiceNettValue] - shows total invoice amount
  • [PostingNettValue] - shows amount paid by customer at each date

 

 

Thank you for any help you may be able to provide.

1 ACCEPTED SOLUTION

Hey there,
I asked this question about two months ago, so sorry I forgot to mark it. I didn't end up putting together a graph exactly like I outlined in the original question, but I have a few things I could share with you.

 

What I ended up doing was creating a calculated column for each invoice line item that displayed days from invoice date to payment date; another for invoice date to due date; another that compared the two for a total overdue days per invoice; and then some measures using the AVG() function to get average numbers of overdue days per customer, per month, etc. 

 

It's not exactly the same as running a full Aged Trial Balance for any point in time dynamically, but it allowed me to display some interesting insights - the report can show, on average, how good or bad a particular customer has been at paying their invoices on time, as well as the customer base as a whole, and which months and quarters have been particularly bad. I realized that an ATB for any point in time is not a super useful feature to have - ATBs are used so a company knows who they need to contact most urgently for overdue invoices, and where/when their Accoutns Receivable income will be coming from.

 

Hope that's helpful for you at all. 

View solution in original post

4 REPLIES 4
sajidtp
Frequent Visitor

HI @NickMorrell  could you solve this - please share, been looking for this a while now.

Hey there,
I asked this question about two months ago, so sorry I forgot to mark it. I didn't end up putting together a graph exactly like I outlined in the original question, but I have a few things I could share with you.

 

What I ended up doing was creating a calculated column for each invoice line item that displayed days from invoice date to payment date; another for invoice date to due date; another that compared the two for a total overdue days per invoice; and then some measures using the AVG() function to get average numbers of overdue days per customer, per month, etc. 

 

It's not exactly the same as running a full Aged Trial Balance for any point in time dynamically, but it allowed me to display some interesting insights - the report can show, on average, how good or bad a particular customer has been at paying their invoices on time, as well as the customer base as a whole, and which months and quarters have been particularly bad. I realized that an ATB for any point in time is not a super useful feature to have - ATBs are used so a company knows who they need to contact most urgently for overdue invoices, and where/when their Accoutns Receivable income will be coming from.

 

Hope that's helpful for you at all. 

Hi @NickMorrell , would you mind sharing sample model you have. I have tried implementing DSO as metric by this wonderful post by Sakiko Stickely meausring reducing DSO on PowerPivotPro but still miss ATB.

 

I have a particular need of comparing debt vs. sale over a period of time (now done putting together aggregated monthly closing balances separately but this requires mantaining a massive model) and advise business on reviewing credit terms. This is a standard staple of review reports and would have liked to have it very much 😉

v-yulgu-msft
Employee
Employee

Hi @NickMorrell ,

 

Please provide some simplified data, if your data model contains sensitive data that cannot be shared, you could provide dummy data. And show us your desired output for better understanding.

How to Get Your Question Answered Quickly

 

Regards,

Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.