cancel
Showing results for
Did you mean: 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 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 % =
SUMX(
FILTER(
'Dates',
'Dates'[Date] = [*MinDate]
),
)
SUMX(
FILTER(
'Dates',
'Dates'[Date] = [*MaxDate]
),
)

RETURN
DIVIDE(
[*Max Drawdown],
DIVIDE(
2
))``````
2 REPLIES 2 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. 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 % =
SUMX(
FILTER(
'Dates',
'Dates'[Date] = [*MinDate]
),
)
SUMX(
FILTER(
'Dates',
'Dates'[Date] = [*MaxDate]
),
)

RETURN
DIVIDE(
[*Max Drawdown],
DIVIDE(
2
))``````   