I hate IRR - Can you help me again?

[Virtual_mini_me]

Hi all,

After losing some hours on this issue, I need your help as I am loosing some marbles and thinking of giving up.
Any gentle soul around?

I have the following table:

And I need to calculate the iterative IRR for each fund at each date.
The problem is the following: the XIRR formula accept the two arguments: Dates and Values.

I tried to use CALCULATETABLE( SUMMARIZE to get only the 'summarized table of data for each company" and I tried to "union" two summarized table (one for the last value of the IRR and one for the cashflows), but PowerBI crashes.

With the example table above, I managed to calculate the following IRRs in excel:

Can you help to find a formula for a new column (or a Measure?)

Thank you

[v-tangjie-msft · Accepted solution]

Hi @Virtual_mini_me ,

We can create a table for each company.

Table_AAA = FILTER('Table',[Company]="AAA")

Table_AAA = FILTER('Table',[Company]="BBB")

Table_AAA = FILTER('Table',[Company]="CCC")

IRR_AAA = 
XIRR (
    'Table_AAA',
    'Table_AAA'[Net Cashflow],
    RANK ( DENSE, ALL('Table_AAA'[Cashflow Date]))
        * 365
)

For more details, please refer:

XIRR function (DAX) - DAX | Microsoft Learn

Excel Magic Trick 1430: DAX Functions XNPV & XIRR for Irregular Cash Flow Net Present Value & IRR (y...

Learn Power BI: DAX Fridays! #142: IRR, XIRR and XNPV in DAX – Power BI Every Day

If this does not achieve your desired results, please provide your formulas and sample data in EXCEL (pasted in text format rather than images).

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.