cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JamesB86
Regular Visitor

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

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; 
 
DateRegionModelTypeModel_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
 
DatePortfolio APortfolio B
30-Sep-20-1.02%1.35%
31-Aug-201.88%0.15%
31-Jul-201.86%0.85%
30-Jun-201.63%0.97%
31-May-201.89%-0.80%
30-Apr-202.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-192.11%0.55%
30-Nov-190.77%2.29%
31-Oct-190.41%-1.82%
30-Sep-190.52%0.69%
31-Aug-19-0.58%0.20%
31-Jul-190.07%0.10%
30-Jun-192.04%2.11%
31-May-19-1.64%1.89%
30-Apr-190.73%2.81%
31-Mar-191.44%0.41%
28-Feb-190.55%0.52%
31-Jan-192.29%-0.58%
31-Dec-18-1.82%0.07%
30-Nov-180.69%2.04%
31-Oct-18-2.35%-1.64%
30-Sep-180.20%0.73%
31-Aug-180.10%1.44%
31-Jul-181.35%2.04%
30-Jun-180.15%-1.64%
31-May-180.85%0.73%
30-Apr-180.97%1.44%
31-Mar-18-0.80%0.55%
28-Feb-18-1.40%0.20%
31-Jan-180.84%0.10%
31-Dec-171.60%1.35%
30-Nov-17-0.02%0.15%
31-Oct-172.11%0.85%
30-Sep-170.77%0.97%
31-Aug-170.41%0.41%
31-Jul-170.52%0.52%
30-Jun-17-0.58%2.29%
31-May-170.07%-1.82%
30-Apr-172.04%0.69%
31-Mar-17-1.64%0.20%
28-Feb-170.73%0.10%
31-Jan-171.44%-1.64%
31-Dec-160.55%0.73%
30-Nov-162.29%1.44%
31-Oct-16-1.82%2.04%
30-Sep-160.69%-1.64%
31-Aug-160.20%0.41%
31-Jul-160.10%0.52%
30-Jun-161.35%-0.58%
31-May-160.15%-1.64%
30-Apr-160.85%0.73%
31-Mar-160.97%1.44%
29-Feb-16-0.80%2.04%
31-Jan-162.04%-1.64%
31-Dec-15-1.64%0.41%
30-Nov-150.73%0.52%
31-Oct-151.44%-0.58%
30-Sep-150.55%0.07%
31-Aug-152.29%2.29%
31-Jul-15-1.82%-1.82%
30-Jun-150.69%0.69%
31-May-150.20%0.20%
30-Apr-150.10%0.10%
31-Mar-152.11%2.11%
28-Feb-151.89%1.89%
31-Jan-152.81%2.81%

 

Portfolio PerformancePortfolio APortfolio BFormula
1 Year4.15%6.17%=IFERROR(PRODUCT(1+B2:B13)-1,"")
3 Year3.99%7.72%=IFERROR(PRODUCT(1+B2:B37)^(1/3)-1,"")
5 Year4.58%5.77%=IFERROR(PRODUCT(1+B2:B61)^(1/5)-1,"")
    
Tracking ErrorPortfolio APortfolio BFormula
1 Year8.67%4.59%=STDEV(B2:B13)*SQRT(12)
3 Year6.00%4.02%=STDEV(B2:B37)*SQRT(12)
5 Year5.22%4.14%=STDEV(B2:B61)*SQRT(12)
    
Information RatioPortfolio APortfolio BFormula
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. 
1 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

48.png

 

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.

 

View solution in original post

5 REPLIES 5
JamesB86
Regular Visitor

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. 

JamesB86_0-1620643601745.png

 

=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

48.png

 

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.

 

View solution in original post

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:

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
Community Support
Community Support

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors