DAX Function for Payment Date and Due Date of Invoices in Power BI

This document outlines a DAX function that calculates both the payment date and the due date of invoices and consolidates these dates into a single slicer for visualization in Power BI.

Overview

The function will:

• Calculate the due date based on the invoice date and payment terms.
• Calculate the payment date based on the invoice date and payment completion date.
• Return a single table containing both dates for use in slicers.

Necessary Imports

No additional libraries are needed as this is a pure DAX function for use within Power BI.

Code Implementation

Below is the DAX code that creates a calculated table for the payment and due dates of invoices.

PaymentAndDueDates = 
VAR InvoicesTable = 'Invoices'  // Assuming you have a table called 'Invoices'
RETURN 
    SUMMARIZE(
        InvoicesTable,
        InvoicesTable[InvoiceID],                 // Unique identifier for each invoice
        InvoicesTable[InvoiceDate],               // Original invoice date
        InvoicesTable[PaymentTerms],              // Payment terms (e.g., number of days to pay)
        InvoicesTable[PaymentCompletionDate],     // Actual payment date
        "DueDate", EDATE(InvoicesTable[InvoiceDate], InvoicesTable[PaymentTerms]),
        "PaymentDate", InvoicesTable[PaymentCompletionDate]
    )

Function Explanation

• SUMMARIZE: This function creates a new table summarizing the unique rows of the invoice table based on specified columns.
• VAR: Variable to reference the invoices table, making the code cleaner.
• EDATE: Calculates the due date by adding the payment terms (in months) to the invoice date.
• Return Columns:
  • InvoiceID: Unique identifier for invoices.
  • InvoiceDate: The date the invoice was issued.
  • PaymentTerms: Days until payment is due.
  • PaymentCompletionDate: The date when the invoice has been paid.
  • DueDate: Calculated by adding the payment terms to the invoice date.
  • PaymentDate: Directly from the PaymentCompletionDate column.

Input Validation

• The function assumes the existence of columns in the 'Invoices' table.
• Ensure that PaymentTerms is a numeric value representing the number of days until the payment is due.

Usage Instructions

1. Paste the provided DAX code into the DAX formula bar in Power BI.
2. Create a visual using the PaymentAndDueDates table and include a slicer from either the DueDate or PaymentDate columns.
3. Use this slicer to filter reports and dashboard visuals based on invoice payment status and timelines.

Example Scenario

Assume you have an Invoices table with the following columns:

• InvoiceID: 001
• InvoiceDate: 2023-01-01
• PaymentTerms: 30 days
• PaymentCompletionDate: 2023-01-25

When the DAX code runs, it will create a new table that includes:

• InvoiceID: 001
• InvoiceDate: 2023-01-01
• DueDate: 2023-01-31 (30 days after InvoiceDate)
• PaymentDate: 2023-01-25

This table can then be used in Power BI slicers for effective reporting.

Conclusion

This DAX function efficiently prepares a consolidated table of payment and due dates which can be utilized for filtering and slicers in Power BI reports. For comprehensive learning on DAX and Power BI, consider the courses offered by the Enterprise DNA Platform.