Invoice Balance Calculation Formula Explanation

Formula in Appropriate Language

InvoiceBalance = SUM(CustomerLedgerEntries[Amount]) 
    - CALCULATE(SUM(CustomerLedgerEntries[Amount]), 
        FILTER(CustomerLedgerEntries, CustomerLedgerEntries[TransactionDate] > SpecificDate))

Formula Explanation

• InvoiceBalance: Calculates the total balance in the customer ledger entries as of a specific date.

• SUM(CustomerLedgerEntries[Amount]): Sums up the total amount in the customer ledger entries.

• CALCULATE(SUM(CustomerLedgerEntries[Amount]), FILTER(...)): Calculates the sum of the amount after applying a filter based on the transaction date being greater than the specific date.

• FILTER(CustomerLedgerEntries, CustomerLedgerEntries[TransactionDate] > SpecificDate): Filters the customer ledger entries to consider only those with transaction dates greater than the specific date.

Practical Illustration

If we have a customer ledger with transaction amounts on different dates:

• Transaction on 2022-01-10: $100
• Transaction on 2022-02-15: $150
• Transaction on 2022-03-20: $80

For a specific date input of 2022-02-01, the formula will calculate the Invoice Balance as: $100 (Total Amount) - $0 (Amount after 2022-02-01) = $100

So, the Invoice Balance as of 2022-02-01 will be $100.