Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors