cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Absalon29
Helper III
Helper III

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

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

Please try my custom visuals


View solution in original post

16 REPLIES 16
Zubair_Muhammad
Community Champion
Community Champion

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

Please try my custom visuals


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

Hi @Absalon29

 

Please could you share your file via Onedrive or GoogleDrive?

Regards
Zubair

Please try my custom visuals


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

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.

 

 

Regards
Zubair

Please try my custom visuals


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

Please try my custom visuals


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

@Absalon29

 

I thought its beginning of the year....Smiley Tongue

 

I will fix it in a while hopefully

Regards
Zubair

Please try my custom visuals


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

Please try my custom visuals


View solution in original post

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. 

Hi @Absalon29,

 

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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. 

 

 

 

Hi,

 

You may refer to my solution in this workbook.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

HI @Absalon29

 

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

Regards
Zubair

Please try my custom visuals


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors