Calculate XIRR in DAX

DAX Formula for Calculating XIRR

To calculate the XIRR (Extended Internal Rate of Return) in DAX and ensure that transactions and distributions are sorted by date, you can utilize the following DAX formula to create a calculated table that organizes and calculates the XIRR.

Formula

XIRR_Calculation = 
VAR SortedTransactions = 
    ADDCOLUMNS (
        FILTER (
            UNION (
                SELECTCOLUMNS(Transactions, "Date", Transactions[TransactionDate], "Amount", Transactions[TransactionAmount]),
                SELECTCOLUMNS(Distributions, "Date", Distributions[DistributionDate], "Amount", -Distributions[DistributionAmount])  // Negative for outflows
            ),
            NOT(ISBLANK([Date]))
        ),
        "Date", [Date],
        "Amount", [Amount]
    )
VAR SortedData = 
    SUMMARIZE (
        SortedTransactions,
        [Date],
        "TotalAmount", SUM([Amount])
    )
RETURN 
    XIRR(SortedData[TotalAmount], SortedData[Date], 0.1)  // Assumes a guess of 10% for the initial XIRR guess

Explanation

1. Creating a Combined Table:

   • The UNION function combines two tables: Transactions and Distributions.
   • SELECTCOLUMNS extracts relevant columns for both: TransactionDate and TransactionAmount for transactions, and DistributionDate (negative because distributions are cash outflows) and DistributionAmount.

2. Filtering Non-Blank Records:

   • FILTER ensures that only non-blank records are retained, crucial for proper calculations.

3. Summarizing Data:

   • The SUMMARIZE function groups the combined data by Date and calculates TotalAmount.

4. Calculating XIRR:

   • The XIRR function computes the internal rate of return with SortedData[TotalAmount] representing cash flow amounts and SortedData[Date] their corresponding dates. An optional 0.1 is used as an initial guess for the XIRR calculation.

Practical Example

Assuming you have two tables: Transactions and Distributions.

• Transactions Table:

  TransactionDate	TransactionAmount
  2022-01-01	1000
  2022-06-01	500

• Distributions Table:

  DistributionDate	DistributionAmount
  2022-12-01	300
  2023-06-01	200

Steps:

1. Create a new calculated table in Power BI and paste the DAX formula above.
2. Replace TransactionDate, TransactionAmount, DistributionDate, and DistributionAmount with your actual column names.
3. The result will be a calculated table that holds the XIRR value based on the provided transactions and distributions, keeping them sorted by date.

Conclusion

This approach enables you to calculate XIRR by managing investments and cash flows in DAX effectively while ensuring the correct chronological order. For further learning on advanced calculations like XIRR or other DAX functions, consider additional training resources offered by the Enterprise DNA Platform.