Formula Generator

Formula for Invoice Balance Extraction

Summarize 'Customer Ledger Entries' to calculate 'Invoice Balance' by filtering data based on 'Document Type' and 'Posting Date'. A practical tool to extract total invoice balance up to a specific date.

Prompt

To extract invoice balance in customer ledger entries as on specific date

Answer

Formula for Extracting Invoice Balance in Customer Ledger Entries

SUMMARIZECOLUMNS (
    'Customer Ledger Entries',
    "Invoice Balance",
    CALCULATE (
        SUM ('Customer Ledger Entries'[Amount]),
        'Customer Ledger Entries'[Document Type] = "Invoice" &&
        'Customer Ledger Entries'[Posting Date] <= SpecificDateParameter
    )
)

Explanation:

Use the SUMMARIZECOLUMNS function to create a table summarizing data.
Filter the 'Customer Ledger Entries' table to include only rows with a 'Document Type' of "Invoice" and a 'Posting Date' less than or equal to the specific date.
Calculate the sum of the 'Amount' column for these filtered rows to get the 'Invoice Balance' as of the specific date.

Practical Illustration:

If 'Customer Ledger Entries' table contains columns: 'Document Type', 'Amount', 'Posting Date'.
If SpecificDateParameter is set to 31st March 2022.
The formula will return the total balance of all invoices in the 'Customer Ledger Entries' table up to 31st March 2022.