XIRR Measure Question

[smitpau]

Hi,

I'd like to be able to display an IRR table with the Month as the column and Company name as the row.

Now this partly works, as by company name is working as you can see below.

The issue is when adding any date dimension type for the column, it throws up the error message below.

Does anybody know if this is an inherent issue with the XIRR measure, the column MonthYear in this case is in the date table which is linked via a relationship to the table with the values used to calculate the IRR.

BTW the total of 32.3% is correct as some IRR's would be negative but Power BI I don't believe can display those so they show as "-" instead.

The raw Power BI data is in the Excel linked below which has the data for the XIRR columns and the Date table it has the relationship with.

XIRR Data.xlsx

Matrix Working - Without date column

Matrix Not Working - With Data Column

Visualisation Inputs

IRR Measure

[smitpau]

Thanks Owen, that works perfectly. Always humbled to receive help like this.

PS your LinkedIn URL isn't working at the moment.

[OwenAuger · Accepted solution]

Hi @smitpau 

I've had a quick look.

By the sounds of it, you want to compute a Cumulative IRR at any given date.

I've attached a sample PBIX, with a data model based on your Excel data.

Here are the measures I created:

Cashflow Sum = 
SUM ( Cashflow[Cashflow] )

In IRR base, there is a check that the Cashflow table is nonempty before computing XIRR.

I've used SUMMARIZE to produce a table of dates that exist in the Cashflow table, to avoid iterating over redundant dates.

I also recommend not returning 0 when the IRR can't be computed, but rather BLANK as this will improve performance. But you can change this back to zero if you prefer.

IRR base = 
IF (
    -- Only calculate IRR if Cashflow is nonempty
    NOT ISEMPTY ( Cashflow ),
    XIRR (
        -- Just Dates with Cashflow
        SUMMARIZE ( Cashflow, 'Date'[Date] ),
        [Cashflow Sum],
        'Date'[Date],
        ,
        -- return BLANK if no IRR can be found
        BLANK ()
    )
)

IRR Cumulative computes IRR up to the max filtered date. It also uses the earliest existing date as a lowerbound for performance reasons.

IRR Cumulative = 
-- Min date existing in Cashflow table with Date filters removed
VAR MinDate =
    CALCULATE (
        MIN ( Cashflow[Date] ),
        REMOVEFILTERS ( 'Date' )
    )
VAR MaxDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [IRR base],
        DATESBETWEEN ( 'Date'[Date], MinDate, MaxDate )
    )

Is this the sort of thing you were looking for?

I would also recommend filtering out zero values from the Cashflow data (if that won't cause any other issues) to help performance.

Regards