Forum Discussion

jan1's avatar
jan1
Regular Visitor
10 years ago
Solved

Calculating drawdown - running total with condition

Hello, I would like to calculate drawdown on trading account, quite easy in excel, not very easy in Power BI for me.

 

I have following data set Trade No., Net profit and I would like to calculate Balance and Drawdown. Balance is simple running total, so it is not a problem. But I have no idea how to calculate drawdown – drawdown from previous highest high.

 

Drawdown is calculated as Previous Drawdown plus Net Profit, but only if result is lower than 0 otherwise drawdown is 0.

 

Example:

 

Trade NoNet profitBalanceDrawdown
11001000
2-5050-50
3-100-50-150
4500-100
55050-50
6501000
71002000
8-100100-100
950150-50
102003500

 

 Thank you for any help.

  • OwenAuger's avatar
    OwenAuger
    10 years ago

    Hi jan1,

     

    You could do something similar to this (alter to match your table names etc). Create the following measures:

    (sample PBIX file here)

     

     

    NetProfit =
SUM ( 'Trades'[Net profit] )

Balance = 
CALCULATE (
    [NetProfit],
    FILTER ( ALL ( 'Trades' ), 'Trades'[Trade No] <= MAX ( 'Trades'[Trade No] ) )
)

High Water Mark = 
MAXX (
    ADDCOLUMNS (
        FILTER ( ALL ( 'Trades' ), 'Trades'[Trade No] <= MAX ( 'Trades'[Trade No] ) ),
        "Bal", [Balance]
    ),
    [Bal]
)

Drawdown =
    [Balance] - [High Water Mark]

     

  • OwenAuger's avatar
    OwenAuger
    10 years ago

    Hi jan1,

     

    You should be able to do this using ALLEXCEPT.

     

    Is everything in the SumDataStrategies table?

     

    In my earlier formulas, change ALL(...) to ALLEXCEPT('SumDataStrategies', 'SumDataStrategies'[Strategy ID])

     

    Something like that should work :)

     

4 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    10 years ago

    Hi jan1,

     

    You could do something similar to this (alter to match your table names etc). Create the following measures:

    (sample PBIX file here)

     

     

    NetProfit =
SUM ( 'Trades'[Net profit] )

Balance = 
CALCULATE (
    [NetProfit],
    FILTER ( ALL ( 'Trades' ), 'Trades'[Trade No] <= MAX ( 'Trades'[Trade No] ) )
)

High Water Mark = 
MAXX (
    ADDCOLUMNS (
        FILTER ( ALL ( 'Trades' ), 'Trades'[Trade No] <= MAX ( 'Trades'[Trade No] ) ),
        "Bal", [Balance]
    ),
    [Bal]
)

Drawdown =
    [Balance] - [High Water Mark]

     

    • jan1's avatar
      jan1
      Regular Visitor
      10 years ago

      Hi OwenAuger,

      you just made my day, thank you.

       

      Could you help me on one more thing, I would like to extend the dataset and add new column - Strategy ID.

      And I would like to calculate Balance and Drawdown not only for entire dataset but also according to Strategy ID.

      I tried to modify Balance and High Water Mark measure by extending filter using && ('SumDataStrategies'[Strategy ID] = 'SumDataStrategies'[Strategy ID]).

       

      It is not working.

       

      Thanks.

      • OwenAuger's avatar
        OwenAuger
        Icon for Super User rankSuper User
        10 years ago

        Hi jan1,

         

        You should be able to do this using ALLEXCEPT.

         

        Is everything in the SumDataStrategies table?

         

        In my earlier formulas, change ALL(...) to ALLEXCEPT('SumDataStrategies', 'SumDataStrategies'[Strategy ID])

         

        Something like that should work :)