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 ...
  • 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 :)

     

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

     

    • jan1's avatar
      jan1
      Regular Visitor
      10 years ago

      I modified ALL to ALLEXCEPT and it works, except when Highwatermark was lower than 0.

       

      So I modified Drawdown to this:

      DrawdownStrategy = IF([HighWaterMarkStrategy] >= 0; [BalanceStrategy] - [HighWaterMarkStrategy]; [BalanceStrategy])

       

      Now it works perfectly.

      Thanks OwenAuger