I’ve got debtor transactions in a general ledger table where the system doesn’t show that a transaction has been cleared. It does, but it is not usable.
I’ve highlighted the amounts that are not cleared, but is there a way to create a calculated column that would indicate that these are “Unpaid” and mark the other transactions as “Paid”.
I want to created an aged debtors report from the data.
Thanks in advance.
Just a couple of things with your post.
Can you please add more information about the support request in the post itself, rather than just adding a file. This will help those who are reviewing the request to understand what exactly it is about.
Also you have added an Excel file. As we are all working in Power BI please add and pbix file.
Please see below for further details on asking effective question in the forum.
https://forum.enterprisedna.co/t/rules-and-tips-for-asking-support-questions-on-the-forum/30
Thanks
Sam
Hi there, normally with accounting systems tables the general ledger is not what is used to analyse sales invoice data.
You need to query the Sales table as each invoice balance will normally be listed here. There is no need normally to create this kind of calculated column that will blow up the size of your model.
Check with your software provider to determine which ledger you need to query!
If you provide the name of your accounting system, I can perhaps advise you more.
Good Luck!
Garry
HI Gary & Sam,
sorry for the late reply as I have been working on other things. My original question arises from a Infor SunSystems V6.1 accounting package that I was working on. I have to say that it is a very poor implementation. They don’t have subsidiary ledgers for the debtor and creditor transactions - they are all combined into the GL. In the GL, the account code column has both account codes and debtor and creditor accounts. They also don’t use PowerBI - so that is why I was using Excel and I am still learning Power BI.
Thanks for your responses, but I will let this question fade for the time being,
I think you will find all accounting systems have sub ledgers.
The general ledger is actually made up of many sub ledgers.
Perhaps look for a column name Journal Type or Source of the entry. Sales transactions for example normally a SJ (Sales Journal), CR (Cash Receipts), PJ (Purchase Journals) and GJ (General Journals).
I would suggest contacting the software vendor to confirm which table contains the sales data. Normally large accounting systems like these are either on premise SQL based or if cloud based you can use an API to query the relevant data.
Good Luck and I hope that helps!