I need help creating an efficient calculation for drawdown and maximum drawdown in an equity curve (which is a cumulative sum of net profit or revenue, etc). A drawdown is a measure of decline in an equity curve from a peak to a trough and the maximum drawdown (MaxDD) is simply the largest drawdown in the equity curve.
This metric is most commonly used in the financial trading/investing industry to calculate the actual or potential risk in trading system or portfolio, but can really be used for anything such as customer revenue, accounts receivables, etc.
For my dashboard, besides profit this is the most important metric as it represents risk and I can’t figure out an efficient calculation for this! I think the screenshot below provide more clarification on how this is calculated and what this looks like.
There are essentially 3 main calculations that can be derived from drawdown and then you take the max(or min if drawdown is shown as a negative number) to determine the maximum drawdown.
Profit (in the data model table), Cumulative Profit, Peak Profit, Drawdown, maxDD
The dashboard has aprox 20K rows of data (which is not a lot of data) and each row represents a trade that has profit, date, system name, type of strategy, etc. I figured out how to return the correct maximum drawdown figure dynamically in the PowerBI dashboard, but is not usable since it is so inefficient so probably best to start over!
Let me know if you need any other information or pbx files. Really appreciate any assistance I can get!