Posted Invoice Aggregator (Bevica Power BI) (Optional)

Optional model table that groups item ledger entries to posted invoices for analysis.

Posted Invoice Aggregator (Bevica Power BI) (Optional)

Available 09/2025 releases and later only

Overview

The way Business Central posts to the item ledgers and value entries can make it more difficult to analyse transactions at a detailed level. For example, viewing additional costs against a sales order. This table can be used to aggregate or group transactions to an invoice.

Implementation

There are several parts to this:

• Summarise the item ledger table by

  • ILE Entry No
  • Document No (Posted Sales Invoices, Sales Credit Memos, Purchase Invoices, Purchase Credit Memos) to provide a list of posted "invoices"
• Summarise non-inventory documents (e.g. invoices for a GL account)

  • Entry No (this is generated as part of the data load)
  • Document No
• Identify the first posted document against an Entry No as the aggregation

This aggregated table can then be used to filter the Item Ledger table to show the invoice it was against.

The table is called Posted Invoice Aggregated.

Default available fields are:

• Document No (Posted Docs)
• Document Type (Posted Docs)
• (PK) Item Ledger Entry No - hidden by default, used as the relationship to the Item Ledger Entry table
• Posting Date (Posted Docs) - hidden by default

Known behaviours and potential issues

Business Central can post the following which could cause this grouping to be inaccurate:

• One shipment with multiple invoices

  • The shipment creates the stock movement (item ledger entry) but subsequent invoices create multiple invoices against the individual item ledger.
  • The first invoice only will be aggregated.
• Multiple shipments

  • The shipments will create an entry no and invoices would be linked to them "one to one" so it should work as expected (i.e. separate posted docs).
• Cost adjustment credits using item charges

  • These do not create a stock movement but an adjustment against the original movement.
  • The item charge is linked to the original shipment and therefore the original item ledger entry number. The credit will therefore appear under the original invoice.
• Sales return orders (i.e. those returning stock)

  • These will create a new item ledger for the return so will be against the sales credit memo created.
• Documents crossing periods

  • For any of the above scenarios, a document could post in a different period. Where it is grouped then it could resurface a transaction.
  • The 'Posting Date' of the grouped invoice is available but not shown by default. The standard relationship for dates is the Item Ledger Entry Posting Date.

Overcoming known limitations

• Use the "Document No (Posted Docs)" and the "Document No" from the Item Ledger Entry table to show the specific documents
• The default date filter from the item ledger entry (posting date) can be used to surface the differences for different periods if reconciling
• Ensure setup in BC uses inventory periods and robust posting date controls
• Review how you use BC around part shipments/invoices to be aware of how this can affect reporting