XIRR Measure Question

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