I have a requirement to reproduce Sage 50 reporting in Power BI, but as I am not an accountant, or very familiar with Sage 50, the Aged Debtors and Creditors reports are proving difficult. I have reproduced them for the current date, but the method for 'ageing' them seems obscure.
I imagine I need to use 2 queries, but don't really know where to start. Which fields need to be amended and where can I find the data to 'Exclude later transactions'?
I would expect to be able to identify 'Later' transactions using the user input date and then to use those transactions to modify the results from the existing query, but I don't know how to find those later transactions or how to use them to modify my existing query.
The syntax for my existing Creditors query is:
= Odbc.Query("dsn=ACCOUNT v28", "SELECT COMPANY.NAME, AUDIT_SPLIT.TYPE, AUDIT_SPLIT.Date, AUDIT_SPLIT.ACCOUNT_REF, AUDIT_SPLIT.INV_REF, AUDIT_SPLIT.EXTRA_REF, AUDIT_SPLIT.DETAILS, AUDIT_SPLIT.DISPUTED, AUDIT_SPLIT.PAID_FLAG, AUDIT_SPLIT.DELETED_FLAG, AUDIT_SPLIT.AGED_BALANCE, AUDIT_SPLIT.AGED_FUTURE, AUDIT_SPLIT.AGED_CURRENT, AUDIT_SPLIT.AGED_30, AUDIT_SPLIT.AGED_60, AUDIT_SPLIT.AGED_90, AUDIT_SPLIT.AGED_OLDER#(lf)FROM AUDIT_SPLIT, COMPANY#(lf)WHERE (((AUDIT_SPLIT.TYPE) Like ""P_"") AND ((AUDIT_SPLIT.DISPUTED)=0) AND ((AUDIT_SPLIT.PAID_FLAG)=""N"") AND ((AUDIT_SPLIT.DELETED_FLAG)=0));")
I apply the date on the user screen, but it's only really accurate when including all transactions to current date.
Many thanks for your help.
