Forum Discussion
Annual Returns
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
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
Anonymous
I thought its beginning of the year....:smileytongue:
I will fix it in a while hopefully
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 ) ) - 1Hi 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.
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
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))
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.
