Table of XIRR for Each Quarter (Value and Dates arguments empty)

[NorthW]

I'm trying to create a simple table of IRRs for an investment at each quarter end (as below)

The challenge is that the data includes an investment value at each quarter end but I only want the calculation to include the most recent quarter end valuation ("NAV"). See the sample of the date below:

The IRR measure I've created is as follows:

IRR =

VAR mdate = MAX('Fund Operations'[Date])

Return

Calculate(

xirr('Fund Operations','Fund Operations'[Cashflows],'Fund Operations'[Date]),

filter(ALLEXCEPT('Fund Operations','Fund Operations'[Fund]),

'Fund Operations'[Operation Type] = "Drawn Capital" && 'Fund Operations'[Date] <= DATEVALUE(mdate)||'Fund Operations'[Operation Type] = "NAV" &&

'Fund Operations'[Date] = DATEVALUE(mdate)

))

I'm trying to retain the filter on Fund because there are  multiple funds in the data table so I want the report filter to be kept and only run the calculation on the specified fund. I'm then trying to take the quarter end date and pick up all cashflows up to that date for the "drawn capital" cash flows but only the "NAV" at the quarter end.  If I create a dummy table with this filter it works fine (although i have to hard code mdate), similarly if i remove the calculation and just return mdate I can see that the correct date is being picked up each quarter. For some reason though when the filter is added using the mdate variable i get an error to tell me the XIRR dates and cash flows are empty.  Any help would be appreciated,  I can't think of any other ways to check the calculation logic and resolve.

[NorthW]

Hi,  Thanks for your message,  I tried to use your measure but get the same error still.  To explain further what i'm trying to do,  the NAVs are for the same fund but at different times.  I'm trying to get the report to generate the below tables and calculate the IRRs at the different dates and then display them in one table.  So using the data in my initial question, the first date would be December : 

Then for march the calculation would ignore the december 2019 cash flow and use the nav in March :

Finally in June it would ignore both previous NAV cash flows:

The output would then look like this:

Any additional help you could provide would be appreciated.  Thanks!

[MagicLantern]

I've been trying to solve this exact issue recently - can I ask if you managed to resolve the issue in the end?

[Anonymous]

Hi @NorthW ,

Not very clear.

If you want to calculate different XIRR based on each Fund, please try:

IRR = 
VAR mdate =
    MAX ( 'Fund Operations'[Date] )
RETURN
    CALCULATE (
        XIRR (
            'Fund Operations',
            'Fund Operations'[Cashflows],
            'Fund Operations'[Date]
        ),
        FILTER (
            'Fund Operations',
            [Fund]
                IN ALLSELECTED ( 'Fund Operations'[Fund] )
                    && [Operation Type] = "Drawn Capital"
                    && [Date] <= DATEVALUE ( mdate )
                    || [Operation Type] = "NAV"
                        && [Date] = DATEVALUE ( mdate )
        )
    )

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