Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |