Automated elimination entries for consolidation using Power Automate and D365 data entities

ERP Microsoft Dynamics 365
, , , , , , , ,
1

Sharing our implementation of automated intercompany elimination entries that reduced our monthly close time by 3 days. Previously, our accounting team manually created elimination journal entries for intercompany transactions across 8 legal entities - a process taking 15-20 hours each month and prone to errors.

We built a Power Automate solution that leverages D365’s data entities to identify intercompany transactions, calculate elimination amounts, and automatically post elimination entries. The flow triggers on the 3rd business day of each month and processes all prior month intercompany activity. Key components include automated data entity queries, Power Automate flow orchestration, and validation of intercompany elimination rules before posting. Happy to detail our approach for others facing similar consolidation challenges.

2

Let me walk through our complete implementation to address the questions about data entities, triggers, and validation:

Data Entity Automation: We use three core D365 data entities accessed via OData endpoints:


// Pseudocode - Entity queries in Power Automate:
1. Query GeneralJournalAccountEntry entity
   - Filter: PostingDate >= FirstDayPriorMonth AND PostingDate <= LastDayPriorMonth
   - Filter: IntercompanyAccountType != 'None'
2. Query VendInvoiceJour for AP intercompany
   - Filter: InvoiceDate in prior month range
   - Filter: TradingPartnerCode is not null
3. Query CustInvoiceJour for AR intercompany
   - Filter: InvoiceDate in prior month range
   - Filter: IntercompanyOriginalCompany is populated
// Combine results into consolidated dataset

The queries return all potentially intercompany transactions. We then apply business logic to match trading partner pairs.

Power Automate Triggers: Our flow structure:

  • Trigger: Recurrence - Daily at 6:00 AM
  • Action 1: Get items from SharePoint business calendar list (filters for current date)
  • Condition: If calendar item BusinessDayOfMonth = 3, continue; else terminate
  • Action 2: Set variables for prior month date range
  • Action 3-5: Query the three data entities (parallel branches for performance)
  • Action 6: Parse and combine results into array
  • Action 7-12: Transformation and validation logic
  • Action 13: Post elimination entries via LedgerJournalEntity
  • Action 14: Send summary email to accounting team

The business calendar approach gives us flexibility - if month-end close is delayed, accounting can adjust the calendar and the flow adapts automatically.

Intercompany Elimination Rules: Our validation logic implements these rules before posting:

  1. Balance Validation: For each intercompany pair (e.g., Entity A ↔ Entity B), sum all transactions. The net should be near zero (we allow $10 tolerance for rounding). If imbalance exceeds tolerance, flag for review.

  2. Trading Partner Matching: Verify each transaction has a corresponding offset in the partner entity. For example, Entity A’s intercompany payable should match Entity B’s intercompany receivable. Unmatched transactions are logged for manual investigation.

  3. Account Mapping Validation: Check that elimination accounts exist in the consolidation legal entity. Our flow maintains a mapping table (SharePoint list) of intercompany accounts to elimination accounts.

  4. Period Lock Check: Query D365 to ensure the target period is open for posting. If locked, send alert to accounting and terminate flow.

The actual elimination entry creation uses the LedgerJournalEntity with this pattern:

  • Journal Name: AUTO_ELIM (configured in D365)
  • Description: “Automated IC elimination - [Month/Year]”
  • Lines: Debit/Credit pairs that reverse the intercompany balances
  • Approval workflow: Entries post to a staging journal that requires controller approval via D365’s workflow

Results and Lessons Learned:

  • Time savings: Reduced from 15-20 hours to approximately 2 hours (mostly review time)
  • Accuracy improvement: Eliminated manual data entry errors
  • Audit trail: Every flow run creates detailed logs in SharePoint, including which transactions were processed and elimination amounts
  • Edge cases: We still manually handle foreign currency intercompany transactions due to complexity of exchange rate adjustments

One critical lesson: Start with read-only validation flows before automating the posting. We ran our flow in “validation mode” for two months, comparing its calculations to manual eliminations, before enabling automatic posting. This built confidence and helped us identify edge cases.

The entire solution uses only standard Power Automate connectors (D365 Finance & Operations, SharePoint, Office 365) - no custom connectors or premium licenses beyond what’s included in our E5 licensing. Development time was about 40 hours spread over 3 weeks, with another 40 hours for testing and refinement.

3

This sounds exactly like what we need. Can you share which specific data entities you’re querying for intercompany transactions? We’ve struggled to identify all intercompany activity reliably - sometimes transactions are coded correctly, sometimes not.

4

We query three primary entities: GeneralJournalAccountEntry for GL transactions, VendInvoiceJour for AP intercompany invoices, and CustInvoiceJour for AR intercompany invoices. The key is filtering by the IntercompanyAccountType field and matching trading partner relationships. We also implemented data quality checks - the flow flags transactions without proper intercompany coding for manual review before processing eliminations.