cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CNH1
Frequent Visitor

Profit drawdown against weighted average portfolio

I am trying to get a formula that shows the drawdown on an investment portfolio from a Peak profit to a minimum profit following that peak.

 

I have created measures that show

  1. Maximum cumulated Profit – field [**Max Profit]

 

** Max Profit =

Maxx(

     ALLSELECTED(Dates),

     [*Profit running total in Date]

    )

 

  1. Minimum cumulated Profit – field [**Min Profit]

 

** Min Profit =

Minx(

     ALLSELECTED(Dates),

     [*Profit running total in Date]

    )

 

  1. The maximum drawdown Field [**Max Drawdown] is the difference between (1) and (2) above

 

** Max Drawdown = [** Max Profit] - [** MIN Profit]​

 

  • I have a Portfolio total value Field is [Total Value]

 

Total Value = ROUND([Equity Value] + [Cash Value], 2)​

 

 

  • I have drawings and incoming funds under Field [Deposits]

 

Deposits = 
IF(VALUES(Config[TrackCash])="Yes"
  , CALCULATE([Trans Total Amnt], TransType[DepositTransSign] = 1)
   -CALCULATE([Trans Total Amnt], TransType[DepositTransSign] = -1)
)​

 

  • I wish to create a measure that takes the [Total value] at the maximum profit that is adjusted by [Deposits] thereafter until the minimum vale is reached. This is therefore the [**Adjusted Portfolio Value]

 

** Portfolio Adjusted = [Total Value]+[Deposits]​

This measure I would need to create some date logic to pick [Total Value] at date of maximum profit??

 

  • The aim then is to calculate the Drawdown % against weighted average [**Adjusted Portfolio Value].

I am attaching link to an excel spreadsheet that is showing my calculations in excel.
Drawdown data.xlsx

I am trying to code this [ie. drawdown % against weighted average portfolio] in Power BI but it is beyond my capacities.

I’d appreciate guidance.

1 ACCEPTED SOLUTION
CNH1
Frequent Visitor

I have not been able to get to the Weighted average, calculated on a daily basis. Given the materiality I have decided to use the average of the opening and closing. As such this is where I have finished

*Max Drawdown % = 
VAR PortfolioAdjustedMIN =
   SUMX(
      FILTER(
         'Dates',
            'Dates'[Date] = [*MinDate] 
        ),
         [*Portfolio Adjusted] 
    )
VAR PortfolioAdjustedMAX =
    SUMX(
        FILTER(
            'Dates',
            'Dates'[Date] = [*MaxDate] 
        ),
         [*Portfolio Adjusted] 
    )

RETURN
    DIVIDE(
        [*Max Drawdown],
        DIVIDE(
        PortfolioAdjustedMAX + PortfolioAdjustedMIN,
        2
    ))

View solution in original post

2 REPLIES 2
CNH1
Frequent Visitor

I continued to work on this question.

I have created the following measures:

 

**MaxDate = MAXX(FILTER(ALLSELECTED(Dates), [*Profit running total in Date] = [** Max **bleep** Profit]), Dates[Date]) 
**MinDate = MAXX(FILTER(ALLSELECTED(Dates), [*Profit running total in Date] = [** MIN **bleep** Profit]), Dates[Date]) 
***Drawdown Days = Datediff([**MaxDate],[**MinDate],DAY)

 

So what I am now trying to achieve is to sum ‘Report’[** Portfolio Adjusted] between the **MaxDate and **MinDate

This is the point I am struggling with. I have tried various DAX code, with out success. One of which is

 

SUMX(FILTER(ALLSELECTED('Dates'),'Dates'[Date] >= [**MinDate] && 'Dates'[Date] <= [**MaxDate]),'Report'[**Portfolio Adjusted])

 


This is returning (Blank).   Can someone direct me on a correction

 

Once I resolve this I will then divide [**MaxDrawdown] by this sum.

Hopefully someone can point me in the right direction.

CNH1
Frequent Visitor

I have not been able to get to the Weighted average, calculated on a daily basis. Given the materiality I have decided to use the average of the opening and closing. As such this is where I have finished

*Max Drawdown % = 
VAR PortfolioAdjustedMIN =
   SUMX(
      FILTER(
         'Dates',
            'Dates'[Date] = [*MinDate] 
        ),
         [*Portfolio Adjusted] 
    )
VAR PortfolioAdjustedMAX =
    SUMX(
        FILTER(
            'Dates',
            'Dates'[Date] = [*MaxDate] 
        ),
         [*Portfolio Adjusted] 
    )

RETURN
    DIVIDE(
        [*Max Drawdown],
        DIVIDE(
        PortfolioAdjustedMAX + PortfolioAdjustedMIN,
        2
    ))

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors