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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.