Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Annual Returns

I am trying to calculate a fund's yearly performance based on monthly returns. In excel we calculate the yearly performance with the formula B12/B2-1 which gives us the total performance for 2015 of -3.86% (see attached photo).  How would we calculate this formula in dax? 

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi Anonymous

     

    HOw abou this one?

     

    YTD_Zubair =
VAR Previous_YEAR_Closing =
    CALCULATE (
        MAX ( EMCD[Date] ),
        EMCD[Date] < EARLIER ( EMCD[Date] )
            && YEAR ( EMCD[Date] )
                = YEAR ( EARLIER ( EMCD[Date] ) ) - 1
    )
RETURN
    DIVIDE (
        EMCD[Absalon EM Corporate Debt SICAV NAV],
        CALCULATE (
            SUM ( EMCD[Absalon EM Corporate Debt SICAV NAV] ),
            EMCD[Date] = Previous_YEAR_Closing
        )
    )
        - 1

16 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Hi Anonymous

     

    Try this calculated Column to get YTD return.

     

    =
VAR Beginning_Value =
    CALCULATE (
        VALUES ( Table1[NAV] ),
        FILTER ( ALL ( Table1 ), Table1[Date] = MIN ( Table1[Date] ) )
    )
RETURN
    DIVIDE ( Table1[NAV], Beginning_Value ) - 1
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks Zubair, 

       

      I'm clearly doing something wrong as I get the following error. What is causing the circular dependency? Can you point me in the right direction please?

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Hi Anonymous

         

        Please could you share your file via Onedrive or GoogleDrive?