Profit drawdown against weighted average portfolio

[CNH1]

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.

[CNH1]

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 · Accepted solution]

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