XIRR calculation using DAX

[rk_nithi]

Hello,

i need your support to calculate XIRR values of my mutual fund investment. I have a table as below

Date	Transaction Amount	Transaction Type	Investment Category
05-12-2019	-10000	Buy	Debt
08-12-2019	-90000	Buy	Debt
23-12-2019	50000	Sell	Debt
14-01-2020	40000	Sell	Debt
25-02-2020	-10000	Buy	Equity
02-03-2020	-50000	Buy	Debt
03-03-2020	-5000	Buy	Equity
09-03-2020	-2500	Buy	Equity
13-03-2020	-2500	Buy	Equity
24-03-2020	-2500	Buy	Equity
03-04-2020	-2500	Buy	Equity
15-04-2020	-11500	Buy	Debt
16-04-2020	-6500	Buy	Equity
14-06-2020	-232000	Buy	Debt
05-08-2020	306027.52	Sell	Debt
10-08-2020	-99995	Buy	Debt
31-08-2020	100142.49	Sell	Debt
10-09-2020	-99995	Buy	Debt
21-09-2020	25000	Sell	Debt
11-01-2021	25000	Sell	Debt
26-01-2021	50859.19	Sell	Debt
03-02-2021	6.88	Sell	Debt
02-03-2021	-9999.5	Buy	Equity
31-03-2021	-9999.5	Buy	Equity
05-04-2021	-49997.5	Buy	Equity
07-04-2021	-9999.5	Buy	Equity
12-04-2021	-7999.6	Buy	Equity
28-04-2021	-9999.5	Buy	Equity
07-05-2021	-9999.5	Buy	Equity
28-05-2021	-9999.5	Buy	Equity
08-06-2021	-9999.5	Buy	Equity
28-06-2021	-9999.5	Buy	Equity
07-07-2021	-9999.5	Buy	Equity
27-07-2021	-9999.5	Buy	Equity
09-08-2021	-9999.5	Buy	Equity
16-11-2021	59160.07	Sell	Equity
22-11-2021	-24998.75	Buy	Equity
17-12-2021	-9999.5	Buy	Equity
20-12-2021	-9999.5	Buy	Equity
27-01-2022	-4999.75	Buy	Equity
01-02-2022	-9999.5	Buy	Debt
07-02-2022	-4999.75	Buy	Equity
10-02-2022	-4999.75	Buy	Equity
15-02-2022	-24998.75	Buy	Debt
16-02-2022	-4999.75	Buy	Equity
18-02-2022	-24998.75	Buy	Equity
28-02-2022	-4999.75	Buy	Equity
01-03-2022	-9999.5	Buy	Debt
10-03-2022	-4999.75	Buy	Equity

my question is how to calculate XIRR of my investments in the above format? also the current market value is not in the transaction table mentioend above and it is in different table. how to bring these tables together and calculate the XIRR value?

Thanks

[Ohouot225]

This is my proposition:

Create a single table in Power Query.

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

Date	Transaction Type	Cashflow
20/11/2025	Overall Portfolio	95.000

•      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.

[v-menakakota]

Hi @rk_nithi ,

Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank @Ohouot225   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

Best Regards, 
Community Support Team 

[Ohouot225]

• 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.

[rohit1991]

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]) 
)

[rk_nithi]

Hi Rohit1991,

can you please share the pbix file for better understanding?

[rohit1991 · Accepted solution]

Hii @rk_nithi 

Here the link of my pbix file :

https://netorgft12209796-my.sharepoint.com/:u:/g/personal/rohit91_oxygenbi_com/EVnRPLWGNnVAuYMdprYfV... 

[Ohouot225]

This is my proposition:

Create a single table in Power Query.

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

Date	Transaction Type	Cashflow
20/11/2025	Overall Portfolio	95.000

•      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.

[Hans-Georg_Puls]

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"?

[rk_nithi]

Hi DNMAF,
Thanks for the reply, in my table only transactions of bought and sold details are available and you know the value of previous investments would change as per per market value. So the current value of total investments would be different than the invested amount. to calculate overall return on investment , i think we should calculate difference between current market value and invested value right? my question is in the XIRR formula there is no possibility to consider current market value, how to accommodate that?

[Hans-Georg_Puls]

Hi @rk_nithi ,

to include the current market value you could do the following (assuming that the current market values are positive and the table with the market values has a date column):

1. Create a table with all relevant values using UNION: (your column and table names might be different)
   All Values = UNION(SELECTCOLUMNS('Market Value', "Date", [Date], "Value", [Market Value]), SELECTCOLUMNS('Transaction', "Date", [Date], "Value", [Transaction Amount]))
2. Define an appropriate measure like the following:
   XIRR with market values =
   VAR MinDate = CALCULATE(MIN('All Values'[Date]), REMOVEFILTERS('All Values'))
   VAR MaxDate = SELECTEDVALUE('All Values'[Date], MAX('All Values'[Date]))
   RETURN
       CALCULATE(
           XIRR( 'All Values', [Value], [Date],, BLANK()),
           REMOVEFILTERS('All Values'),
           'All Values'[Date] >= MinDate && 'All Values'[Date] <= MaxDate
       )

Does that fulfil your requirements?