cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Absalon29 Member
Member

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? 

 

Annual performance.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Annual Returns

Hi @Absalon29

 

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
Try my new Power BI game Cross the River

View solution in original post

16 REPLIES 16
Super User
Super User

Re: Annual Returns

Hi @Absalon29

 

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
Try my new Power BI game Cross the River
Absalon29 Member
Member

Re: Annual Returns

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?

 

Column Variable.JPG

Super User
Super User

Re: Annual Returns

Hi @Absalon29

 

Please could you share your file via Onedrive or GoogleDrive?

Try my new Power BI game Cross the River
Absalon29 Member
Member

Re: Annual Returns

Absalon29 Member
Member

Re: Annual Returns

Sorry the last one didn't seem to work hopefully this does https://1drv.ms/x/s!AiRs1BCuVWhM6EfOsWkw52AbDaq-

Super User
Super User

Re: Annual Returns

Hi @Absalon29

 

This is an Excel file.

The problem (i believe) is in your PBIX file (the image you shared) which uses mutliple tables. I was looking for that file.

 

 

Try my new Power BI game Cross the River
Absalon29 Member
Member

Re: Annual Returns

Super User
Super User

Re: Annual Returns

Hi @Absalon29

 

Please see the file attached here

 

The earliest month of 2015 is empty so I filtered the year 2015 out

 

YTD_Zub =
VAR Previous_Date =
    CALCULATE (
        MIN ( EMCD[Date] ),
        EMCD[Date] < EARLIER ( EMCD[Date] )
            && YEAR ( EMCD[Date] ) = YEAR ( EARLIER ( EMCD[Date] ) )
    )
RETURN
    DIVIDE (
        EMCD[Absalon EM Corporate Debt SICAV NAV],
        CALCULATE (
            SUM ( EMCD[Absalon EM Corporate Debt SICAV NAV] ),
            EMCD[Date] = Previous_Date
        )
    )
        - 1
Try my new Power BI game Cross the River
Absalon29 Member
Member

Re: Annual Returns

Thanks, that is very helpful. The answer is though not quite right because the YTD return for 2016 should be 16.41%. That is calculated by taking the nav on 30/12/2016 (the closing price for 2016) 11191.0 dividing it by the nav on 31/12/2015 9613.7 (the closing price for 2015) minus 1 . So I am looking for a variable that calculates the nav at the end of a year and divide that number by the nav at the end of previous year -1

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)