Annual Returns

[Anonymous]

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? 

[Zubair_Muhammad]

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]

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]

Hi @Anonymous

Please could you share your file via Onedrive or GoogleDrive?

[Anonymous]

Hopefully this works? https://1drv.ms/x/s!AiRs1BCuVWhM6EfOsWkw52AbDaq- 

[Anonymous]

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

[Zubair_Muhammad]

Hi @Anonymous

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.

[Anonymous]

Here is the pbix file https://1drv.ms/u/s!AiRs1BCuVWhM6ElX82MVsTRvdPr5

[Zubair_Muhammad]

Hi @Anonymous

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

[Anonymous]

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

[Zubair_Muhammad]

@Anonymous

I thought its beginning of the year....

I will fix it in a while hopefully

[Zubair_Muhammad · Accepted solution]

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

[Anonymous]

Hi Zubair, 

Thanks so much for all your help, that worked just as I hoped. I now need to understand exactly how you have done it. The path to learning Dax is full of ups and downs! 

Many thanks again. 

[Ashish_Mathur]

Hi @Anonymous,

Here's another solution

Monthly NAV opening

= if(ISBLANK(LASTNONBLANK(EMCD[Date],Sum(EMCD[Absalon EM Corporate Debt SICAV NAV]))),BLANK(),if(HASONEVALUE(DimDate[MonthsofYr]),Sum(EMCD[Absalon EM Corporate Debt SICAV NAV]),CALCULATE(Sum(EMCD[Absalon EM Corporate Debt SICAV NAV]),DATESBETWEEN(DimDate[Date],FIRSTNONBLANK(EMCD[Date],Sum(EMCD[Absalon EM Corporate Debt SICAV NAV])),EOMONTH(FIRSTNONBLANK(EMCD[Date],Sum(EMCD[Absalon EM Corporate Debt SICAV NAV])),0)))))

Monthly NAV closing

=if(ISBLANK(LASTNONBLANK(EMCD[Date],Sum(EMCD[Absalon EM Corporate Debt SICAV NAV]))),BLANK(),if(HASONEVALUE(DimDate[MonthsofYr]),Sum(EMCD[Absalon EM Corporate Debt SICAV NAV]),CALCULATE(Sum(EMCD[Absalon EM Corporate Debt SICAV NAV]),DATESBETWEEN(DimDate[Date],EOMONTH(LASTNONBLANK(EMCD[Date],Sum(EMCD[Absalon EM Corporate Debt SICAV NAV])),-1)+1,LASTNONBLANK(EMCD[Date],Sum(EMCD[Absalon EM Corporate Debt SICAV NAV]))))))

MonthlyRtn

= if(HASONEVALUE(DimDate[MonthsofYr]),IF(COUNTROWS(EMCD)>0,
DIVIDE([MonthlyNAV opening],[PriorMonthNAV],BLANK())-1),if(ISBLANK([MonthlyNAV opening]),BLANK(),[MonthlyNAV closing]/[MonthlyNAV opening]-1))

[Anonymous]

Hi Ashish, 

Thanks. From the monthly NAV figure I am looking to calculate the cumulative yearly return which in 2015 would be 9613 /10000 -1. The calculation sadly doesn't match the correct answer I'm looking for. The Yearly figures in 2015 should be -3.86%, 2016 16.41%, YTD 2017 6.78% see attached spreadsheet which shows the calculation in excel https://1drv.ms/x/s!AiRs1BCuVWhM6FxhfrjpXpf5kU-H Also I should highlight that I am using a seperate date file and am not relying on the date in the EMCD model. 

[Ashish_Mathur]

Hi,

You may refer to my solution in this workbook.

Hope this helps.

[Zubair_Muhammad]

HI @Anonymous

With time and practise, you will be a master in DAX soon.

This book by Rob Collie and Avi Singh is very good as well

https://www.amazon.com/Power-Pivot-BI-Excel-2010-2016/dp/1615470395