Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 No | Net profit | Balance | Drawdown |
1 | 100 | 100 | 0 |
2 | -50 | 50 | -50 |
3 | -100 | -50 | -150 |
4 | 50 | 0 | -100 |
5 | 50 | 50 | -50 |
6 | 50 | 100 | 0 |
7 | 100 | 200 | 0 |
8 | -100 | 100 | -100 |
9 | 50 | 150 | -50 |
10 | 200 | 350 | 0 |
Thank you for any help.
Solved! Go to Solution.
Hi @jan1,
You could do something similar to this (alter to match your table names etc). Create the following measures:
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]
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 🙂
Hi @jan1,
You could do something similar to this (alter to match your table names etc). Create the following measures:
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]
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.
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 🙂
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |