Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dproctor
New Member

Calculate annualized returns using quarterly returns

Hello,

 

A bit new to Power BI, but so far enjoying it! I would appreciate if anyone was able to help me with a question I have about calculating a measure (or measures) using DAX. 

 

Here is a sample data set. My dataset has two funds (Fund A and Fund B) with their quarterly returns shown. These returns are for real estate portfolios, so each fund has an income return + appreciation return = total return for each quarter.

 

Fund NameDateIncome Return (%)Appreciation Return (%)Total Return (%)
Fund A6/30/20211.0%2.0%3.0%
Fund A3/31/20211.5%1.0%2.5%
Fund A12/31/20201.3%1.3%2.6%
Fund A9/30/20200.9%1.2%2.1%
Fund A6/30/20201.1%1.5%2.6%
Fund A3/31/20202.0%1.3%3.3%
Fund A12/31/20191.0%1.0%2.0%
Fund A9/30/20191.4%1.3%2.7%
Fund B6/30/20211.0%0.5%1.5%
Fund B3/31/20211.0%0.81.8%
Fund B12/31/20202.0%2.0%4.0%
Fund B9/30/20203.0%1.0%4.0%
Fund B6/30/20202.1%0.5%2.6%
Fund B3/31/20201.0%1.4%2.4%
Fund B12/31/20191.5%1.5%3.0%
Fund B9/30/20191.2%1.3%2.5%

 

I'd like to calculate annualized returns using these quarterly returns, so that I can compare the performance of Fund A to Fund B over a one year and two year time period. This is the formula that I found from Investopedia:

 

annualized return.PNG

 

 

 

 

 

 

 

 

 

My goal is to output a table like the below, where I can then use Power BI to create some visual charts based on the annualized returns. In the table below, I want to add a column that defines which type of return is being annualized (income, appreciation, or total), and then two more columns that have 1 year annualized returns and 2 year annualized returns, based on the quarterly returns above. Obviously the "X.X%" below are placeholders for whatever gets calculated.

 

Fund NameDateType1 Year Annualized Return2 Year Annualized Return
Fund A6/30/2021incomeX.X%X.X%
Fund A6/30/2021appreciationX.X%X.X%
Fund A6/30/2021totalX.X%X.X%
Fund A3/31/2021incomeX.X%X.X%
Fund A3/31/2021appreciationX.X%X.X%
Fund A3/31/2021totalX.X%X.X%
Fund B6/30/2021incomeX.X%X.X%
Fund B6/30/2021appreciationX.X%X.X%
Fund B6/30/2021totalX.X%X.X%
Fund B3/31/2021incomeX.X%X.X%
Fund B3/31/2021appreciationX.X%X.X%
Fund B3/31/2021totalX.X%X.X%

 

Any help creating this formula is greatly appreciated! Let me know if you have any questions about my question, or if I can provide more detail or examples. I have no idea if this is something that Power BI is capable of, or if I need to do these calculations in Excel before pulling into Power BI.

 

I should note, I do have a custom add-in in my Excel called "linkedann" that does this exact calculation for me, but I couldn't figure out if there was a way to transfer that add-in from Excel to Power BI. The linkedann function was installed by my organization, so not sure the specifics but I do have the ".xla" file handy if that helps. Otherwise, trying to build this from scratch.

 

Thanks!

1 REPLY 1
wdx223_Daniel
Super User
Super User

you can use the function of PRODUCTX

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors