UNION 2 tables but select only most recent date by row item from one table (to use in IRR function)

[BenNorris]

Hi all,

I'm trying to calculate an IRR on a set of data. The data lives in two tables: one contains cashflows and the other contains asset values. The data relates to a number of assets that are grouped into portfolios. Example data as follows:

Cashflow data:

Portfolio	Asset	Cashflow	Date
X	A	-50	01/01/2019
X	A	25	08/06/2020
X	A	-10	13/03/2021
X	A	20	05/05/2021
Y	B	-22	01/02/2021
Y	B	-5	08/03/2021
Y	B	4	05/06/2021
X	C	-40	09/11/2020
Y	D	-80	01/12/2020
Y	D	6	06/03/2021

Asset value data:

Portfolio	Asset	Value	Date
X	A	100	31/12/2020
X	A	105	31/03/2021
X	A	110	30/06/2021
Y	B	25	31/03/2021
Y	B	28	25/05/2021
X	C	50	31/12/2020
X	C	53	31/03/2021
X	C	51	05/04/2021
Y	D	80	31/12/2020
Y	D	79	05/05/2021

My report is set up such that I can pick any date to evaluate the IRR (called the ReportEndDate). Note that the date table does not have any relationship to either of the above tables, nor can it. The measure is being used in a matrix that groups Assets up into Portfolios, so it needs to work at both the portfolio and asset levels.

Once the ReportEndDate is selected, in order to work out the IRR for a single asset, I need to take all of the cashflows that occur on or before the ReportEndDate for that asset and then take the single most recent valuation on or before the ReportEndDate and treat it as if it were a positive cashflow occurring on the ReportEndDate.

Portfolio level IRRs can be calculated in largely the same way, by taking all cashflows on or before the ReportEndDate and adding to them the most recent valuation for each fund and again treating them as if they were cashflows occurring on the ReportEndDate.

So for example, if we assume the ReportEndDate is set to 30/4/2021 then the table of values and dates I would want to use for the XIRR for Asset A would be:

Portfolio	Asset	Date	Value
X	A	01/01/2019	-50
X	A	08/06/2020	25
X	A	13/03/2021	-10
X	A	30/04/2021	105

(I have ignored the cashflow on 5/5/21 for Asset A because it is before the ReportEndDate and I have taken the asset value from 31/3/2021 and assigned it a date of 30/4/2021 (i.e. the ReportEndDate).

If I want to work out the IRR for Portfolio X then it would use the following table:

Portfolio	Asset	Date	Value
X	A	01/01/2019	-50
X	A	08/06/2020	25
X	A	13/03/2021	-10
X	A	30/04/2021	105
X	C	09/11/2020	-40
X	C	30/04/2021	51

(for Asset C I have included the one relevant cashflow and the asset value from 5/4/2021 but assigned a date of 30/4/2021).

I can achieve most of what I want to do by using UNION and SELECTCOLUMNS with a FILTER applied as follows (note that this might not actually work as I've extracted it from a more complicated measure in which I also have to convert by an FX rate but I've excluded that for the sake of clarity):

VAR TableForXIRR =

UNION(
    SELECTCOLUMNS(
        FILTER('Cashflows'[Date]<=[ReportEndDate]),
        "Value", 'Cashflows'[Cashflow],
        "Date", 'Cashflows'[Date]
    ),
    SELECTCOLUMNS(
        FILTER('AssetValues', 'AssetValues'[Date]=[ReportEndDate]),
        "Value", 'AssetValues'[Value],
        "Date", [ReportEndDate]
    )
)

//Sort in date order to make sure the IRR function works
VAR TableForXIRR2 = TOPN(COUNTROWS(TableForXIRR),TableForXIRR,[Date])

Return
    IF(COUNTROWS(TableForXIRR2)=0,BLANK(),
        IFERROR(
            XIRR(TableForXIRR2, [Value], [Date]),
            "n/a")
    )

However, the FILTER on the AssetValues table in the DAX above obviously only selects the asset value on the ReportEndDate, not the most recent one, so it doesn't work because there is no asset value for Asset A on 30/4/2021. Elsewhere in my model when I need to pick the most recent asset value, I've only needed to return a single value and so have been using SUMX and unfiltering/refiltering the table to work out the most recent asset value on a row by row basis and it works well (see below for an example), I just can't figure out how to do that inside the measure above:

CALCULATE(
    SUMX('AssetValues',
        VAR LastTXDate =
            CALCULATE(
                LASTDATE('AssetValues'[Date]),
                FILTER(ALL('AssetValues'),'AssetValues'[Date]<=[ReportEndDate])
            )
        RETURN
            IF([Date] = LastTXDate, 'AssetValues'[Value],0)
    )
)

For reference, the expected results matrix would have the following values (for 30/4/2021):

Portfolio	Asset	IRR
X		52.2%
	A	51.0%
	C	67.5%
Y		10.9%
	B	-29.2%
	D	19.8%

Any help would be greatly appreciated, thanks!

[Ashish_Mathur]

Hi,

Please find attached my solution file.

Hope this helps.

[OwenAuger]

Hi @BenNorris 

I have attached a sample PBIX showing how I would approach this, using your data posted above.

UPDATE: Added a 3rd version of the measure that does not rely on relationships with Date table.

In my model, I have:

1. Created inactive relationships between the fact table date columns (Cashflows[Date] & AssetValues[Date]) and 'Date'[Date]. 'Date' is also marked as a date table.
   You don't have to do it this way but I recommend it as it simplifies applying date filters in the measures.
2. Added Asset & Portfolio dimension tables (I think you have these also).

The model looks like this:

I have created three variations of measure to calculate IRR. V3 doesn't rely on the 'Date' table relationships

IRR Measure = 
VAR ReportDate = [ReportEndDate]
VAR CashflowsFiltered =
    CALCULATETABLE (
        SELECTCOLUMNS (
            Cashflows,
            "@Value", Cashflows[Cashflow],
            "@Date", Cashflows[Date]
        ),
        USERELATIONSHIP ( Cashflows[Date], 'Date'[Date] ), -- Activate relationship for this calculation
        'Date'[Date] <= ReportDate
)
-- Calculate the total of all Asset Values per Asset/Portfolio as a single scalar total
VAR AssetValueTotal =
    CALCULATE (
        SUM ( AssetValues[Value] ),
        GENERATE (
            -- For each Asset/Portfolio...
            SUMMARIZE ( AssetValues, Asset[Asset], Portfolio[Portfolio] ),
            -- ...find the last date with a balance on or before ReportEndDate.
            CALCULATETABLE (
                LASTDATE ( AssetValues[Date] ),
                USERELATIONSHIP ( AssetValues[Date], 'Date'[Date] ), -- Activate relationship for this calculation
                'Date'[Date] <= ReportDate
            )
        )
    )
-- AssetValueRow contains a single row if AssetValueTotal is nonblank
-- otherwise contains zero rows.
VAR AssetValueRow =
    FILTER (
        ROW ( "@Value", AssetValueTotal, "@Date", ReportDate ),
        NOT ISBLANK ( AssetValueTotal )
    )
VAR TableForXIRR = UNION ( CashflowsFiltered, AssetValueRow )
RETURN
    IF (
        COUNTROWS ( TableForXIRR ), -- equivalent to COUNTROWS > 0
        IFERROR ( XIRR ( TableForXIRR, [@Value], [@Date] ), "n/a" )
    )

IRR Measure v2 = 
VAR ReportDate = [ReportEndDate]
VAR CashflowsFiltered =
    CALCULATETABLE (
        SELECTCOLUMNS (
            Cashflows,
            "@Value", Cashflows[Cashflow],
            "@Date", Cashflows[Date]
        ),
        USERELATIONSHIP ( Cashflows[Date], 'Date'[Date] ), -- Activate relationship for this calculation
        'Date'[Date] <= ReportDate
    )
VAR AssetValuesDates =
    -- This table will contain columns Asset[Asset], Portfolio[Portfolio], AssetValues[Date]
    -- where Date is the last date with an Asset Value on/before ReportEndDate for a given Asset/Portfolio.
    GENERATE (
        -- For each Asset/Portfolio...
        SUMMARIZE ( AssetValues, Asset[Asset], Portfolio[Portfolio] ),
        -- ...find the last date with a balance on or before ReportEndDate.
        CALCULATETABLE (
            LASTDATE ( AssetValues[Date] ),
            USERELATIONSHIP ( AssetValues[Date], 'Date'[Date] ), -- Activate relationship for this calculation
            'Date'[Date] <= ReportDate
        )
    )
VAR AssetValuesFiltered =
    CALCULATETABLE (
        SELECTCOLUMNS (
            AssetValues,
            "@Value", AssetValues[Value],
            "@Date", ReportDate
        ),
        AssetValuesDates
    )
VAR TableForXIRR =
    UNION ( CashflowsFiltered, AssetValuesFiltered )
RETURN
    IF (
        COUNTROWS ( TableForXIRR ), -- equivalent to COUNTROWS > 0
        IFERROR ( XIRR ( TableForXIRR, [@Value], [@Date] ), "n/a" )
    )

IRR Measure v3 = 
VAR ReportDate = [ReportEndDate]
VAR CashflowsFiltered =
    CALCULATETABLE (
        SELECTCOLUMNS (
            Cashflows,
            "@Value", Cashflows[Cashflow],
            "@Date", Cashflows[Date]
        ),
        Cashflows[Date] <= ReportDate
    )
VAR AssetValuesDates =
    GENERATE (
        SUMMARIZE ( AssetValues, Asset[Asset], Portfolio[Portfolio] ),
        CALCULATETABLE (
            LASTDATE ( AssetValues[Date] ),
            AssetValues[Date] <= ReportDate
        )
    )
VAR AssetValuesFiltered =
    CALCULATETABLE (
        SELECTCOLUMNS (
            AssetValues,
            "@Value", AssetValues[Value],
            "@Date", ReportDate
        ),
        AssetValuesDates
    )
VAR TableForXIRR =
    UNION ( CashflowsFiltered, AssetValuesFiltered )
RETURN
    IF (
        COUNTROWS ( TableForXIRR ),
        IFERROR ( XIRR ( TableForXIRR, [@Value], [@Date] ), "n/a" )
    )

The output appears to match what you were expecting:

Notes on the measures:

• The basic approach is similar to what you were doing, deriving two tables and unioning them, then using that table as the first argument of XIRR.
• Both measures find the relevant "latest" valuation date for each Asset/Portfolio using SUMMARIZE/GENERATE, then apply this as a filter.
• The first measure aggregates the asset values into a single scalar value and creates a single row for this value (since they are all assigned the same date ReportEndDate).
• The second measure leaves the asset values on individual rows.
• To my knowledge, there is no need to attempt to sort the rows of any table expressions within the measures. In general, you can't rely on any table expression following a given sort order in DAX. The XIRR function internally determines the earliest date for the purpose of the calculation. (This differs from Excel's XIRR function.)
• If you prefer not to have the inactive relationships with 'Date', you can apply date filters directly to the fact tables, similarly to your original version.

Does something like the above measures work for you?

Regards