Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Portfolio Returns

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 (...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

  • Anonymous's avatar
    Anonymous
    8 years ago

    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. 

AMM's avatar
AMM
Frequent Visitor
4 years ago

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's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

Try this measure

VAR maxdate =
    MAXX (
        FILTER (  
            CALCULATETABLE(PortfolioPerf, PortfolioPerf[DisplayName] = "Growth"),
            YEAR ( [PerformanceDate] ) = MAX ( DateDim[Year] ) ),
        [PerformanceDate]
    )
  • AMM's avatar
    AMM
    Frequent Visitor
    4 years ago

    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.

  • AMM's avatar
    AMM
    Frequent Visitor
    4 years ago

    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.