Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi All
I am trying to create a DAX to calculate 1Y/3Y/5Y performance metrics on a monthly stream of returns. Annualised performance for those periods, Tracking error/standard deviation calcs and another but that one may well be simple enough.
I can manage it for YTD calcs (see below) but I need to do it for the set period from the most recent date in the dataset.
Date | RegionModelType | Model_Return |
Date | Portfolio A | Portfolio B |
30-Sep-20 | -1.02% | 1.35% |
31-Aug-20 | 1.88% | 0.15% |
31-Jul-20 | 1.86% | 0.85% |
30-Jun-20 | 1.63% | 0.97% |
31-May-20 | 1.89% | -0.80% |
30-Apr-20 | 2.81% | 2.04% |
31-Mar-20 | -6.42% | -1.64% |
29-Feb-20 | -1.46% | 0.73% |
31-Jan-20 | -0.02% | 1.44% |
31-Dec-19 | 2.11% | 0.55% |
30-Nov-19 | 0.77% | 2.29% |
31-Oct-19 | 0.41% | -1.82% |
30-Sep-19 | 0.52% | 0.69% |
31-Aug-19 | -0.58% | 0.20% |
31-Jul-19 | 0.07% | 0.10% |
30-Jun-19 | 2.04% | 2.11% |
31-May-19 | -1.64% | 1.89% |
30-Apr-19 | 0.73% | 2.81% |
31-Mar-19 | 1.44% | 0.41% |
28-Feb-19 | 0.55% | 0.52% |
31-Jan-19 | 2.29% | -0.58% |
31-Dec-18 | -1.82% | 0.07% |
30-Nov-18 | 0.69% | 2.04% |
31-Oct-18 | -2.35% | -1.64% |
30-Sep-18 | 0.20% | 0.73% |
31-Aug-18 | 0.10% | 1.44% |
31-Jul-18 | 1.35% | 2.04% |
30-Jun-18 | 0.15% | -1.64% |
31-May-18 | 0.85% | 0.73% |
30-Apr-18 | 0.97% | 1.44% |
31-Mar-18 | -0.80% | 0.55% |
28-Feb-18 | -1.40% | 0.20% |
31-Jan-18 | 0.84% | 0.10% |
31-Dec-17 | 1.60% | 1.35% |
30-Nov-17 | -0.02% | 0.15% |
31-Oct-17 | 2.11% | 0.85% |
30-Sep-17 | 0.77% | 0.97% |
31-Aug-17 | 0.41% | 0.41% |
31-Jul-17 | 0.52% | 0.52% |
30-Jun-17 | -0.58% | 2.29% |
31-May-17 | 0.07% | -1.82% |
30-Apr-17 | 2.04% | 0.69% |
31-Mar-17 | -1.64% | 0.20% |
28-Feb-17 | 0.73% | 0.10% |
31-Jan-17 | 1.44% | -1.64% |
31-Dec-16 | 0.55% | 0.73% |
30-Nov-16 | 2.29% | 1.44% |
31-Oct-16 | -1.82% | 2.04% |
30-Sep-16 | 0.69% | -1.64% |
31-Aug-16 | 0.20% | 0.41% |
31-Jul-16 | 0.10% | 0.52% |
30-Jun-16 | 1.35% | -0.58% |
31-May-16 | 0.15% | -1.64% |
30-Apr-16 | 0.85% | 0.73% |
31-Mar-16 | 0.97% | 1.44% |
29-Feb-16 | -0.80% | 2.04% |
31-Jan-16 | 2.04% | -1.64% |
31-Dec-15 | -1.64% | 0.41% |
30-Nov-15 | 0.73% | 0.52% |
31-Oct-15 | 1.44% | -0.58% |
30-Sep-15 | 0.55% | 0.07% |
31-Aug-15 | 2.29% | 2.29% |
31-Jul-15 | -1.82% | -1.82% |
30-Jun-15 | 0.69% | 0.69% |
31-May-15 | 0.20% | 0.20% |
30-Apr-15 | 0.10% | 0.10% |
31-Mar-15 | 2.11% | 2.11% |
28-Feb-15 | 1.89% | 1.89% |
31-Jan-15 | 2.81% | 2.81% |
Portfolio Performance | Portfolio A | Portfolio B | Formula |
1 Year | 4.15% | 6.17% | =IFERROR(PRODUCT(1+B2:B13)-1,"") |
3 Year | 3.99% | 7.72% | =IFERROR(PRODUCT(1+B2:B37)^(1/3)-1,"") |
5 Year | 4.58% | 5.77% | =IFERROR(PRODUCT(1+B2:B61)^(1/5)-1,"") |
Tracking Error | Portfolio A | Portfolio B | Formula |
1 Year | 8.67% | 4.59% | =STDEV(B2:B13)*SQRT(12) |
3 Year | 6.00% | 4.02% | =STDEV(B2:B37)*SQRT(12) |
5 Year | 5.22% | 4.14% | =STDEV(B2:B61)*SQRT(12) |
Information Ratio | Portfolio A | Portfolio B | Formula |
1 Year | 0.48 | 1.34 | =IFERROR(F3/F8,"") |
3 Year | 0.66 | 1.92 | =IFERROR(F4/F9,"") |
5 Year | 0.88 | 1.40 | =IFERROR(F5/F10,"") |
Solved! Go to Solution.
Hi, @JamesB86
Try formula as below:
calculated table:
Table 1 = DATATABLE("Date Period",STRING,{{"1 Year"},{"3 Year"},{"5 Year"}})
Measure:
Portfolio_A =
VAR tab1 =
TOPN ( 12, Fact_Table, Fact_Table[Date], DESC )
VAR tab2 =
TOPN ( 36, Fact_Table, Fact_Table[Date], DESC )
VAR tab3 =
TOPN ( 60, Fact_Table, Fact_Table[Date], DESC )
VAR PortfolioA =
SWITCH (
SELECTEDVALUE ( 'Table 1'[Date Period] ),
"1 Year",
PRODUCTX ( tab1, 1 + [Portfolio A] ) - 1,
"3 Year",
PRODUCTX ( tab2, 1 + [Portfolio A] ) ^ ( 1 / 3 ) - 1,
"5 Year",
PRODUCTX ( tab3, 1 + [Portfolio A] ) ^ ( 1 / 5 ) - 1
)
RETURN
IFERROR ( PortfolioA, "" )
Tracking error A =
VAR tab1 =
TOPN ( 12, Fact_Table, Fact_Table[Date], DESC )
VAR tab2 =
TOPN ( 36, Fact_Table, Fact_Table[Date], DESC )
VAR tab3 =
TOPN ( 60, Fact_Table, Fact_Table[Date], DESC )
RETURN
SWITCH (
SELECTEDVALUE ( 'Table 1'[Date Period] ),
"1 Year",
STDEVX.S( tab1, [Portfolio A] )*SQRT(12),
"3 Year",
STDEVX.S( tab2, [Portfolio A] )*SQRT(12),
"5 Year",
STDEVX.S( tab3, [Portfolio A] )*SQRT(12)
)
Information Ratio_A = IFERROR( [Portfolio_A]/[Tracking error A],"")
Result is as follows nad there may be some Accuracy errors
Please check my attached pbix file for more details.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-easonf-msft thanks for getting back to me.
Model_Return is refering to the table where I have presented the headers, the data stream within it is simple what is in Portfolio A/Portfolio B, below is a small screenshot - Regional Model Type is the portfolio type; example data shows the below example which could be considered Portfolio A, there are quite a few others which my slicer will adjust for as the analytics require it. I only included A & B as an example.
=IFERROR(PRODUCT(1+B2:B13)-1,"") in this formula "B" is the return stream of Portfolio A/B (depending on which is being looked at) and each formula adjusted for the 1Y (12 data points) 3Y (36 data poitns) and 5Y (60 data points).
=STDEV(B2:B13)*SQRT(12) Same for this
=IFERROR(F3/F8,"") here you ask about "F" which is the above to results F3 = Portfolio performance, F8 = Tracking Error.
As for expected results - the table where I have populated the formulas show the expected outputs for Portfolio A in the Excel table.
Apologies for the confusion - hope the above helps.
Hi, @JamesB86
Try formula as below:
calculated table:
Table 1 = DATATABLE("Date Period",STRING,{{"1 Year"},{"3 Year"},{"5 Year"}})
Measure:
Portfolio_A =
VAR tab1 =
TOPN ( 12, Fact_Table, Fact_Table[Date], DESC )
VAR tab2 =
TOPN ( 36, Fact_Table, Fact_Table[Date], DESC )
VAR tab3 =
TOPN ( 60, Fact_Table, Fact_Table[Date], DESC )
VAR PortfolioA =
SWITCH (
SELECTEDVALUE ( 'Table 1'[Date Period] ),
"1 Year",
PRODUCTX ( tab1, 1 + [Portfolio A] ) - 1,
"3 Year",
PRODUCTX ( tab2, 1 + [Portfolio A] ) ^ ( 1 / 3 ) - 1,
"5 Year",
PRODUCTX ( tab3, 1 + [Portfolio A] ) ^ ( 1 / 5 ) - 1
)
RETURN
IFERROR ( PortfolioA, "" )
Tracking error A =
VAR tab1 =
TOPN ( 12, Fact_Table, Fact_Table[Date], DESC )
VAR tab2 =
TOPN ( 36, Fact_Table, Fact_Table[Date], DESC )
VAR tab3 =
TOPN ( 60, Fact_Table, Fact_Table[Date], DESC )
RETURN
SWITCH (
SELECTEDVALUE ( 'Table 1'[Date Period] ),
"1 Year",
STDEVX.S( tab1, [Portfolio A] )*SQRT(12),
"3 Year",
STDEVX.S( tab2, [Portfolio A] )*SQRT(12),
"5 Year",
STDEVX.S( tab3, [Portfolio A] )*SQRT(12)
)
Information Ratio_A = IFERROR( [Portfolio_A]/[Tracking error A],"")
Result is as follows nad there may be some Accuracy errors
Please check my attached pbix file for more details.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Just a big thank you for supplying this - works perfectly with my visualisations providing matching results. Is it possible you could explain a bit more about what is happening within the solution? Just so I can get to grips with it!
Hi, @JamesB86
The variable 'tab1'/‘tab2’/'tab3' is a temporary table (data of the previous 12/36/60 months in descending order of Date).
The variable 'PortfolioA' is calculated based on the value of the Date Period of the current row.
If Date Period is "1 year", it will execut
PRODUCTX(tab1, 1 + [Portfolio A])-1
If Date Period is "3 year", it will execut
PRODUCTX(tab2, 1 + [Portfolio A]) ^ (1/3)-1.
If Date Period is "5 year", it will execut
PRODUCTX(tab3, 1 + [Portfolio A]) ^ (1/5)-1.
related function:
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @JamesB86
Sorry, I am still a bit confused about the information you provided. .
Which column in the data table does Master_Table[Model_Return] correspond to?
What does Column F in excel refer to (can you show relevant screenshots)? It would be great if you can show relevant desired results.
Best Regards,
Community Support Team _ Eason
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.