Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 Name | Date | Income Return (%) | Appreciation Return (%) | Total Return (%) |
Fund A | 6/30/2021 | 1.0% | 2.0% | 3.0% |
Fund A | 3/31/2021 | 1.5% | 1.0% | 2.5% |
Fund A | 12/31/2020 | 1.3% | 1.3% | 2.6% |
Fund A | 9/30/2020 | 0.9% | 1.2% | 2.1% |
Fund A | 6/30/2020 | 1.1% | 1.5% | 2.6% |
Fund A | 3/31/2020 | 2.0% | 1.3% | 3.3% |
Fund A | 12/31/2019 | 1.0% | 1.0% | 2.0% |
Fund A | 9/30/2019 | 1.4% | 1.3% | 2.7% |
Fund B | 6/30/2021 | 1.0% | 0.5% | 1.5% |
Fund B | 3/31/2021 | 1.0% | 0.8 | 1.8% |
Fund B | 12/31/2020 | 2.0% | 2.0% | 4.0% |
Fund B | 9/30/2020 | 3.0% | 1.0% | 4.0% |
Fund B | 6/30/2020 | 2.1% | 0.5% | 2.6% |
Fund B | 3/31/2020 | 1.0% | 1.4% | 2.4% |
Fund B | 12/31/2019 | 1.5% | 1.5% | 3.0% |
Fund B | 9/30/2019 | 1.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:
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 Name | Date | Type | 1 Year Annualized Return | 2 Year Annualized Return |
Fund A | 6/30/2021 | income | X.X% | X.X% |
Fund A | 6/30/2021 | appreciation | X.X% | X.X% |
Fund A | 6/30/2021 | total | X.X% | X.X% |
Fund A | 3/31/2021 | income | X.X% | X.X% |
Fund A | 3/31/2021 | appreciation | X.X% | X.X% |
Fund A | 3/31/2021 | total | X.X% | X.X% |
Fund B | 6/30/2021 | income | X.X% | X.X% |
Fund B | 6/30/2021 | appreciation | X.X% | X.X% |
Fund B | 6/30/2021 | total | X.X% | X.X% |
Fund B | 3/31/2021 | income | X.X% | X.X% |
Fund B | 3/31/2021 | appreciation | X.X% | X.X% |
Fund B | 3/31/2021 | total | X.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!
you can use the function of PRODUCTX
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |