Calculate XIRR from two tables

[Nagesh20]

Hi all,

Trying to calcaulate XIRR from the below two tables. It woud be helpful if anyone can provide the right DAX formula.

Getting IRR in excel is 17.13%

Cashflow table

Date           Fund         Cashflow

3/1/2022	Quantum	2000
3/1/2022	LIC	500
4/1/2022	LIC	300
4/1/2022	Quantum	3000

NAV table

Date          Fund       Cashflow

3/1/2024	Quantum	-3000
3/1/2024	Quantum	-4000
3/1/2024	LIC	-900

Relationship

[Nagesh20]

Hi @AmiraBedh 

I am getting the correct IRR number of 17.13% with this formula 

IRR =

VAR Total_Cashflow = UNION(SELECTCOLUMNS(Cashflow,"Cashflow",Cashflow[Cashflow],"date",Cashflow[Date]),

SELECTCOLUMNS(NAV,"Cashflow",NAV[Cashflow],"date",NAV[Date]))

Return

XIRR(Total_Cashflow,[Cashflow],[date])

But when i apply the filter for the specific "Fund", it's getting different IRR number 

In Excel, Getting IRR for Quantum is 18.81%, for LIC is 6.16%

In reality, there are huge list of fund names to filter

Please help me how to get this sorted out.

[Nagesh20]

Hi @AmiraBedh 

Finally it's resolved it by creating table and created a measure function

Transactions_All = UNION(SELECTCOLUMNS(Cashflow,"Cashflow",Cashflow[Cashflow],"date",Cashflow[Date],"Account",Cashflow[Account],"Fund",Cashflow[Fund]),

SELECTCOLUMNS(NAV,"Cashflow",NAV[Cashflow],"date",NAV[Date],"Account",NAV[Account],"Fund",NAV[Fund]))

IRR2 = XIRR(Transactions_All,Transactions_All[Cashflow],Transactions_All[date])

I would like to get IRR reflect in a table with Fund name like below. It would be great if provide a solution on this

Fund	IRR2
Quantum	18.81%
LIC	6.61%

[AmiraBedh · Accepted solution]

Try the following :

IRR2 = 
SUMMARIZE(
    Transactions_All,
    Transactions_All[Fund],
    "IRR", [Your_IRR_Calculation_Measure] // I assume you have a measure that calculates IRR
)

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Show the problem and the expected result clearly.

[AmiraBedh]

You forgot to share the formula 🙂