Portfolio Returns

HI Anonymous,

You can try to use below formula to achieve your requirement:

Measrue:

Result =
VAR maxdate =
    MAXX (
        FILTER ( ALL ( EMCD ), YEAR ( [Date] ) = MAX ( DimDate[Year] ) ),
        [Date]
    )
VAR current_dec =
    SUMX (
        FILTER (
            ALL ( EMCD ),
            FORMAT ( [Date], "yyyy/mm" ) = FORMAT ( maxdate, "yyyy/mm" )
        ),
        [Absalon EM Corporate Debt SICAV NAV]
    )
VAR temp =
    SUMX (
        FILTER (
            ALL ( EMCD ),
            FORMAT ( [Date], "yyyy/mm" )
                = FORMAT ( DATE ( YEAR ( maxdate ) - 1, MONTH ( maxdate ), 1 ), "yyyy/mm" )
        ),
        [Absalon EM Corporate Debt SICAV NAV]
    )
VAR previous_dec =
    IF (
        temp <> BLANK (),
        temp,
        LOOKUPVALUE (
            EMCD[Absalon EM Corporate Debt SICAV NAV],
            EMCD[Date], MINX (
                FILTER ( ALL ( EMCD ), [Absalon EM Corporate Debt SICAV NAV] <> BLANK () ),
                [Date]
            )
        )
    )
RETURN
    IF (
        MAX ( [Absalon EM Corporate Debt SICAV NAV] ) <> BLANK (),
        IF ( current_dec / previous_dec <> BLANK (), current_dec / previous_dec - 1 )
    )

Notice: if your data contains any privacy data, please do mask sensitive data before sharing.

Regards,

Xiaoxin Sheng

Ashish thanks for your help. You have supplied a spreadsheet with the calculation which I already have. What I was looking to do was work out the syntax in Dax to reproduce the excel formula. Running the calculation in excel is straightforward, running it in Dax requires a lot more knowledge of the language eg the use of Earier, etc. I'm not there yet, but thanks for your help.