Forum Discussion
Max Drawdown calculation
I don’t think using ADDCOLUMNS can be a solution since we still need to use the iteration in it. You can try it if possible. I want to know if you want the Cumulative Profit$ and Peak to be changed dynamically based on your selection in slicer? If not, maybe we can create Cumulative Profit$ and Peak as columns instead of measures to avoid iterations.
BTW, what is your CPU and RAM infomation here?
Best Regards,
Herbert
Yes, the calculations need to be dynamically. For instance, the trade results are from a portfolio of different trading systems. The cumulative profit, peak and drawdown calculations need to be sliced by trading system, total portfolio, year, long/short, etc. This probably means that calculated columns are a no-go.
Hardware is a core i7-5820K CPU @3.30GHz, 16GB RAM
The challenge is I think to find a way to iterate only 1 time through the table and during this one iteration calcuate the cumulative profit, peak and drawdown row by row (more or less like you would do in excel).
Just to test, how about the performance when we remove the MINX from the last Max Drawdown measure (only calculate [Cumulative Profit$] - [Peak])?
I tried to sum up the continuous drawdown instead of calculating Cumulative Profit$ and Peak. But I found the result is not correct in some situations. We also cannot create another summarize table since we want it to be dynamically. :manfrustrated:
It seems that we cannot avoid calculating Cumulative Profit$ and Peak here.
Best Regards,
Herbert
Thanks for your efforts. Without the MINX from the last Max Drawdown calculation the performance is reasonable. Still not very fast, but a considerable improvement compared to the full calculation. So then we just have the Drawdown; in what way could we derive the Max Drawdown from this without decreasing performance again?
I read somewhere that Power Pivot/DAX is a column-based tool and one should really avoid doing row-by-row calculations because the performance is terrible. Maybe it works better for these row-by-row calculations to load the data into a sheet instead of the data model and do these calculations with normal excel formulas (allthough a lot of the flexibility is lost then).
If we drag the Drawdown measure into Table visual, how about just sorting this column value? Then we can see the Max Drawdown value in the top.
Best Regards,
Herbert
Then you would have the Max Drawdown over the whole period, not the Max Drawdown until then. I realized the Max Drawdown in my original post was slightly incorrect. The Max Drawdown is in fact also a cumulative value (you want to see the max drawdown until then). So the correct measure is:
Max Drawdown:= MINX(FILTER(ALLSELECTED('Calendar'); 'Calendar'[Date]<=MAX('Calendar'[Date])); CumuProfit-Peak)
where CumuProfit and Peak are other measures or variables.
So if slicing is required then we need the formula with the MINX. A workaround would be to leave the MINX out and accept that we can only see the Max Drawdown for the whole period.
If slicing is required, we should still be able to use the workaroud (leave the MINX out), and we can see the Max Dradown for the selected period as following screenshot.
Best Regards,
Herbert