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. 

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

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.