IRR calculation in different periods

Hi again Caraneda Understood 🙂

First off, I went back and fixed the link on the previous post (tenant/username had changed). If you or anyone else come across one of my old OneDrive links, they can be fixed by changing owenauger_ozerconsulting_onmicrosoft_com to owen_owenaugerbi_com

On your current question, yes you can more-or-less copy the measure from that post to your model, as long as you have a suitable 'Date' table. I have attached a PBIX attempting this with the data you posted.

I would also suggest a couple of tweaks on top of the measure from the other post:

• MaxUnrealisedDate should be calculated per Nemo, just in case the Unrealised dates don't align exactly across Nemos. (assuming this would ever happen)
• To guard against cases where no cashflows exist (for a given set of filters), check if CashflowForIRR is empty, and only return XIRR ( ... ) if it is nonempty.

The tweaked measure is IRR as at latest Unrealised Cashflow v2:

IRR as at latest Unrealised Cashflow v2 = 
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR CashflowForIRR =
    GENERATE (
        VALUES ( 'Cashflow'[Nemo] ),
        VAR MaxUnrealisedDate =
            CALCULATE (
                MAX ( Cashflow[Date] ),
                'Date'[Date] <= MaxDate,
                Cashflow[Status] = "Unrealised"
            )
        RETURN
            CALCULATETABLE (
                ADDCOLUMNS (
                    SUMMARIZE ( Cashflow, 'Date'[Date] ),
                    "CashflowForIRR",
                        VAR StatusFilter =
                            IF ( 'Date'[Date] = MaxUnrealisedDate, "Unrealised", "Realised" )
                        RETURN
                            CALCULATE ( SUM ( Cashflow[Cashflow] ), Cashflow[Status] = StatusFilter )
                ),
                'Date'[Date] <= MaxUnrealisedDate
            )
    )
RETURN
    IF (
        NOT ISEMPTY ( CashflowForIRR ), -- can't calculate in this case
        XIRR ( CashflowForIRR, [CashflowForIRR], 'Date'[Date] )
    )

Regards,

Owen