Aging Report in DAX

Overview

An aging report is essential for tracking the duration that receivables have remained outstanding. This DAX function calculates the aging buckets for customer invoices based on their date and amounts, helping organizations manage their accounts receivable effectively.

Function Definition

Aging Report = 
VAR CurrentDate = TODAY()  // Obtain the current date
VAR AgingTable = 
    ADDCOLUMNS(
        Invoices,  // Table containing invoice data
        "DaysOutstanding", DATEDIFF(Invoices[InvoiceDate], CurrentDate, DAY), // Calculate days outstanding
        "AgingBucket", 
            SWITCH(
                TRUE(),
                [DaysOutstanding] <= 30, "0-30 Days",
                [DaysOutstanding] <= 60, "31-60 Days",
                [DaysOutstanding] <= 90, "61-90 Days",
                "Over 90 Days"  // Default case for invoices older than 90 days
            )
    )
RETURN
    SUMMARIZE(
        AgingTable,  // Summarizing the AgingTable
        [AgingBucket],  // Group by Aging Bucket
        "TotalAmount", SUM(Invoices[Amount])  // Calculate total amounts in each bucket
    )

Code Explanation

1. Current Date Initialization:

   • The variable CurrentDate gets today's date using the TODAY() function.

2. Aging Table Creation:

   • The ADDCOLUMNS function adds two new columns to the Invoices table:
     • DaysOutstanding: Calculates the number of days each invoice has been outstanding.
     • AgingBucket: Categorizes each invoice into aging ranges using SWITCH() based on the outstanding days.

3. Result Summary:

   • SUMMARIZE is used to group the results by AgingBucket and provide the total amount for each bucket.

Input Validation

This DAX code assumes:

• An Invoices table exists with columns InvoiceDate and Amount.
• Ensure that InvoiceDate and Amount do not contain NULLs. This can be handled in the preprocessing of your data model.

Example of Usage

To use the Aging Report measure in Power BI:

1. Insert a Table or Card visualization.
2. Drag the Aging Report measure into the values field.
3. Use the AgingBucket to slice and dice the report further.

Example Usage Code in a Power BI Table

EVALUATE
    [Aging Report]

Output

This will generate output with aging buckets, and the total amounts due in each category effectively.

Conclusion

Implementing an aging report in DAX allows businesses to gain insights into their outstanding invoices easily. For more advanced scenarios or further learning, consider exploring resources on the Enterprise DNA Platform, which offers comprehensive training on DAX and Power BI tools.