cancel
Showing results for
Did you mean:
Highlighted
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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Annual Returns

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
16 REPLIES 16
Super User

## Re: Annual Returns

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
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?

Super User

## Re: Annual Returns

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

## Re: Annual Returns

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
Member

Super User

## Re: Annual Returns

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
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

Announcements