Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / Power BI Model - Outst...
Finance forum
Answered

Power BI Model - Outstanding Customer Invoices

Posted on by 436

I've built a small Power BI model that uses CustAgedBalances entity to do an analysis of customer debt and aging. The entity gives me CustAccount and Custname; however, I want to be able to report which invoice numbers make up the balances for each customer.

Sales Order Headers/Lines and Sales Invoice Headers/Lines will tell me what's been invoiced or part invoiced, but not what's been settled. I can see a reference to the Ledger voucher in for each Sales Invoice Line but no status for it (or the invoice line itself). Is there a way of deducing which vouchers or sales invoice lines are still due (or settled)?

Ian W.

  • Suggested answer
    Ian Waring Profile Picture
    Ian Waring 436 on at
    RE: Power BI Model - Outstanding Customer Invoices

    Hiya,

    For what it's worth, our SI partner Tisski created a custom entity for me that contains the Cust Transactions table in full. I now have this available to download to our BYOD server using Data Management or to selectively access via an ODATA connection. Works really well.

    So, now sorted :-)

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Power BI Model - Outstanding Customer Invoices

    Good morning Ian,

    Another way than creating a new data entity is using electronic reporting.

    With electronic reporting you have access to all tables and can created excel exports to a SharePoint document library that you can integrate with your PowerBI report.

    Maybe this is another option you might want to check out.

    Pretty easy actually.

    Best regards,

    Ludwig

  • Ian Waring Profile Picture
    Ian Waring 436 on at
    RE: Power BI Model - Outstanding Customer Invoices

    Thanks Andre (and indeed Ludwig),

    I'll ask our system integrator (Tisski) if they can add a custom entity encapsulating this for us. It is available on Azure Data Lake with Entity Store Trickle Feed as a Data Flow, but that will then tie us over until that GA's.

    Ian W.

  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 287,914 Super User on at
    RE: Power BI Model - Outstanding Customer Invoices

    Hi Ian,

    There is no out of the box data entity for customer transactions. You have to create a customization to get specific data to your BYOD. E.g. you can create a new entity for open customer invoices where you define a query to include invoices in case the invoice amount is not fully settled yet.

  • Ian Waring Profile Picture
    Ian Waring 436 on at
    RE: Power BI Model - Outstanding Customer Invoices

    Hi Ludwig,

    I'm agonising of how to get Power BI connecting to a table of this name when it doesn't appear in the list of entities I can reach using directquery (on our BYOD instance using the SQLserver connector) or ODATA (direct into AxDW) connections. Or a method of using Power BI embedded native on AxDB/AxDW if we were ever allowed to go in that way (suggested back in the early days of V8.0 but which has never been made available to customers afaik). Data Lake solves it with some available Data Flows but i'm having to rewrite the model over the weekend as all our debt reporting has stopped pending AZDL coming back on stream.

    I have a sneaking suspicion that someone's going to tell me to get Visual Studio out on a dev OneBox to find what I need and to map out the schemas :-}

    Ian W.

  • Verified answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Power BI Model - Outstanding Customer Invoices

    Hi Ian,

    Can't your build your report on the cust trans table?

    From that table you have the linkage to the customer table and get all of the information out that you need?

    Best regards,

    Ludwig

  • Ian Waring Profile Picture
    Ian Waring 436 on at
    RE: Power BI Model - Outstanding Customer Invoices

    Thankyou Andre.

    I can't see a Customer Transactions entity to link to. Only a Customer Payment Journal Lines and Customer Payment Journal Payment Line Settled Invoice one, which appear to be incomplete (lots of gaps). Any idea how I can link to Customer Transactions to see a "Paid/Part Paid/Still Open" status?

    Ian W.

  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 287,914 Super User on at
    RE: Power BI Model - Outstanding Customer Invoices

    Hi Ian,

    The posting of invoices and payments results in customer transactions. The settlement is on the customer transaction level. From the Sales invoice header, you can link to the customer transaction to check if it is an outstanding invoice, paid or partly paid.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,914 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,549 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans