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

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

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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors