Hello Everyone,
I am a beginner to D365 F&O and working on creating Power BI reports for customer transactions.
Currently, I am using Custtrans since this table holds customer transactions. Below is the SQL script I intend to use for my fact table.
I’ve attached a screenshot of the aging period balance report generated in D365 F&O, which I’m trying to replicate in Power BI.
I can successfully retrieve customer balances from CustTrans, but I'm encountering difficulties grouping these transactions into AR aging buckets in Power BI to match the desired output. One challenge is differentiating between paid, unpaid, open, and closed invoices and payments since all related transactions are posted to the customer's account. Unlike SAP, where cleared invoices are removed from the account, D365 F&O appears to retain all transactions or those posting into the system do not clear payment received against invoices.
I suspect that I'm missing essential tables to build a comprehensive fact table for my analysis. I'm currently relying on a view created by a D365 developer, but I lack access to the VM or SSRS to explore further. I need guidance on the necessary tables to create a fact table for this purpose. Thank you for your help.
I also attached the PBIX file and excel file I am using for my report.
select
CT.ACCOUNTNUM AS CUSTOMERID,
DPT.NAME AS CUSTOMERNAME,
ct.DATAAREAID as company,
CT.INVOICE AS INVOICEID,
CT.VOUCHER AS VOUCHER,
cast(CT.TRANSDATE as date) AS TRANSACTIONDATE,
cast(CT.DueDate as date) AS DueDate,
ct.Closed,
CT.AMOUNTCUR AS AMOUNT,
ctt.PaymTermId,
ct.InvoiceTYpe_IT_$Label,
FROM Custtrans CT
Left JOIN CUSTTABLE CTT ON CTT.ACCOUNTNUM = CT.ACCOUNTNUM
INNER JOIN DIRPARTYTABLE DPT ON DPT.RECID = CTT.PARTY
