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

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