Portfolio Returns

[Absalon29]

• I have data covering three years of monthly returns starting in March 2015. 2016 is the only full year of data and  I can't work how to produce a table which has a partial year (2015), a full year (2016) and a year (2017) that requires a YTD calculation. The monthly NAV performance is calculated using (Current month's NAV - Previous Month NAV -1). The table also requires a cumulative total at year end - the cumulative total for the first year is calculated using the Dec 2015 NAV/Feb NAV 2015(the base)-1
• The Data has to be arrange as per the diagram below with the monthly performance (Current Month NAV/ Previous Month NAV-1 for each monthly cell and a cumulative year to date figure for the year
• In a full year the Yearly NAV is calculated as the closing NAV in Dec/Previous Dec's NAV-1

Jan

Feb

March

April

May

June

July

Aug

Sept

Oct

Nov

Dec

Year

• The first NAV is 31st March 2015 10,123.83, Feb is used as the base hence NAV = 10,000
• In order to calculate 2015 NAV I need to divide Dec NAV (9613.70/10,000-1) = -3.86%
• In 2016 I have a full year so the year end calculation would be Dec 2016 closing NAV 11,190.96/Dec 2015 Closing NAV 9613.70 - 1 = 16.41%
• 2017 is also a partial year (the NAV runs up to Sept 2017) so the YTD calculation would be 11,949.31/11,190.96-1=6.78%
• Attached is the monthly returns in excel https://1drv.ms/x/s!AiRs1BCuVWhM6EfOsWkw52AbDaq- 
• Sample PBIX file https://1drv.ms/u/s!AiRs1BCuVWhM6ElX82MVsTRvdPr5

[v-shex-msft · Accepted solution]

HI @Absalon29,

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

[AMM]

I'm trying to adapt this code to a table which has a half dozen funds, so I tried adding a filter on the name of the funds, but I got this error:

Calculation error in measure 'AllMeasures'[Result]: A table of multiple values was supplied where a single value was expected.

So I modified code like this:

VAR maxdate =
    MAXX (
        FILTER (  
            FILTER(PortfolioPerf, PortfolioPerf[DisplayName] = "Growth"),
            YEAR ( [PerformanceDate] ) = MAX ( DateDim[Year] ) ),
        [PerformanceDate]
    )

Do I need to break the table up one per fund, or can I add a filter to this code like I'm attempting?

[Ashish_Mathur]

Hi,

Try this measure

VAR maxdate =
    MAXX (
        FILTER (  
            CALCULATETABLE(PortfolioPerf, PortfolioPerf[DisplayName] = "Growth"),
            YEAR ( [PerformanceDate] ) = MAX ( DateDim[Year] ) ),
        [PerformanceDate]
    )

[AMM]

I tried changing from Filter to CalculateTable and I got the same errror:

Calculation error in measure 'AllMeasures'[OneYearRateForGrowth]: A table of multiple values was supplied where a single value was expected.

And I suppose I can share the whole thing, but it should look very similar I believe we have five stategies in this basket each can be distinquished by a unique name or number.

OneYearRateForGrowth = 
VAR maxdate =
    MAXX (
        FILTER (  
            CALCULATETABLE(PortfolioPerf, PortfolioPerf[DisplayName] = "Growth"),
            YEAR ( [PerformanceDate] ) = MAX ( DateDim[Year] ) ),
        [PerformanceDate]
    )
VAR current_dec =
    SUMX (
        FILTER (
            CALCULATETABLE(PortfolioPerf, PortfolioPerf[DisplayName] = "Growth"),
            FORMAT ( [PerformanceDate], "yyyy/mm" ) = FORMAT ( maxdate, "yyyy/mm" )
        ),
        [CummulativeIndex]
    )
VAR temp =
    SUMX (
        FILTER (
            CALCULATETABLE(PortfolioPerf, PortfolioPerf[DisplayName] = "Growth"),
            FORMAT ( [PerformanceDate], "yyyy/mm" )
                = FORMAT ( DATE ( YEAR ( maxdate ) - 1, MONTH ( maxdate ), 1 ), "yyyy/mm" )
        ),
        [CummulativeIndex]
    )
VAR previous_dec =
    IF (
        temp <> BLANK (),
        temp,
        LOOKUPVALUE (
            PortfolioPerf[CummulativeIndex],
            PortfolioPerf[PerformanceDate], MINX (
                FILTER ( CALCULATETABLE(PortfolioPerf, PortfolioPerf[DisplayName] = "Growth")
                        , [CummulativeIndex] <> BLANK () ),
                [PerformanceDate]
            )
        )
    )
RETURN
    IF (
        MAX ( PortfolioPerf[CummulativeIndex] ) <> BLANK (),
        IF ( current_dec / previous_dec <> BLANK (), current_dec / previous_dec - 1 )
    )

Thanks for the help, but this is kinda not priority number one, so I just quickly tried your code and I keep getting that error.

[AMM]

Thanks I will, my boss actually changed his mind, but yeah we have this basic problem but multiple return streams in the same table. So I thought I could break it up or filter. My DAX was never the best and probably is rusty, thanks a lot.

[Ashish_Mathur]

Hi @Absalon29,

You have started another thread with the same question.  I have answered your question in that thread.  Download my solution from here.

[Absalon29 · Accepted solution]

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. 

[Ashish_Mathur]

Hi,

I have solved the problem using only and only DAX i.e. PowerPivot in MS Excel.  Please check the file at that link.  This entire model can be taken to PowerBI desktop with just one click.

[v-shex-msft · Accepted solution]

HI @Absalon29,

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

[SergioG]

What if the NAV column was based on a measure?

Is it possible to make the calculation?