XIRR calculation using DAX

Hii rk_nithi 

Below are the exact items you must create:

1. Create a Cashflow Column :

Cashflow =
IF(
    'Transactions'[Transaction Type] = "Buy",
    - 'Transactions'[Transaction Amount],
    'Transactions'[Transaction Amount]
)

2.  Create a Measure for Current Market Value

CurrentValue_CF :=
MAX('CurrentValue'[Current Value])

3. Final_CF Column

Final_CF = 'Transactions'[Cashflow]

4. Create the XIRR Measure

XIRR_Investment :=
VAR CF =
    UNION(
        SELECTCOLUMNS(
            ALL('Transactions'),
            "Amount", 'Transactions'[Cashflow],
            "Date", 'Transactions'[Date]
        ),
        ROW(
            "Amount", [CurrentValue_CF],
            "Date", TODAY()
        )
    )
RETURN
IF(
    HASONEVALUE('Transactions'[Date]),
    BLANK(),       
    XIRR(CF, [Amount], [Date]) 
)

Hi rk_nithi ,

according to the definition of the XIRR function (XIRR( CashFlows, [Payment], [Date] )) I would try:

XIRR(Your Table Name, [Transaction Amount], [Date])

Did you already try that?

What do you want to do with the "current market value"?

• Split the columns:

             Make sure that the
Transaction Amount

and

Date

are in separate columns. If this hasn’t been done yet, you’ll need to separate them properly in Power Query.
1. Align the column structure: Ensure that both tables (transactions and current market value) have the same columns, in the same order, with consistent names.
2. Append the tables: Use the Append Queries option in Power Query to merge the two tables into a single table containing all cash flows.
3. Calculate the XIRR: Once the unified table is ready, you can apply the DAX function XIRR() to compute the extended internal rate of return based on the Cash Flow and Date columns.

This is my proposition:

Create a single table in Power Query.

1. Transform the actual value to get a Table like that:

•      Add a custom column with this script 

DateTime.LocalNow()​

• Name the column Date and move at the first column.
• Change the name 'Portfolio' to 'Transaction Type' and 'Current Value' to 'Cashflow', and ensure that the column order follows the same sequence as in the transaction table.

For the transaction Table, you will have something like this:

•   Begin by adding a conditional column for the Cashflow
•   The Append in Power Query to add the Actual value to the Transaction Table (Check the order of the columns before)

Now you can easily use the XIIR function with both table

XIRR_Investment = 
XIRR(Transactions, Transactions[Cashflow], Transactions[Date])

NB: The data I use may differ from yours.