Performance 1Y/3Y//5y DAX Calculations

[JamesB86]

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. 

YTDPerf =

CALCULATE(

PRODUCTX(Master_Table, (1 + Master_Table[Model_Return]))-1,

DATESYTD(Master_Table[Date],"31-12"

))

There are a number of different portfolios that are part of the dataset for similar date periods. I have included an example excel and my Power BI file 'Master_table' headers; 

Date

RegionModelType

Model_Return

Essentially I need to present them in a table similar to the one in my excel example at the bottom, but as long as they can be calculated I will go with "card"'s if i have to.

Excel Data

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

Formula being used in excel as above.

Let me know if you require anything else! Sadly I can't upload files at the minute due to restrictions on our VPN. 

[JamesB86]

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.

[v-easonf-msft · Accepted 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.

[JamesB86]

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!

[v-easonf-msft]

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:

dax/switch-function-dax 

dax/productx-function-dax 

dax/stdevx-s-function-dax 

dax/sqrt-function-dax 

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.

[v-easonf-msft]

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