topic Profit drawdown against weighted average portfolio in DAX Commands and Tips

Profit drawdown against weighted average portfolio

CNH1 — Thu, 23 Mar 2023 03:55:05 GMT

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

Maximum cumulated Profit – field [**Max Profit]

** Max Profit = Maxx( ALLSELECTED(Dates), [*Profit running total in Date] )

Minimum cumulated Profit – field [**Min Profit]

** Min Profit = Minx( ALLSELECTED(Dates), [*Profit running total in Date] )

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.

Re: Profit drawdown against weighted average portfolio

CNH1 — Fri, 31 Mar 2023 04:23:46 GMT

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.

Re: Profit drawdown against weighted average portfolio

CNH1 — Sat, 01 Apr 2023 03:32:51 GMT

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