Max Drawdown calculation

[rahold]

Hello,

I am trying to calculate the Max Drawdown in a table of trades. A maximum drawdown (MDD) is the maximum loss from a peak to a trough of a portfolio, before a new peak is attained.

First, I calculate the cumulative profit with this measure:

Cumulative Profit$:= CALCULATE([TProfit$]; FILTER(ALLSELECTED('Calendar'); 'Calendar'[Date]<=MAX('Calendar'[Date])))

where TProfit$:=SUM(AllTrades[Profit$])

Then, I calculate the peak in the cumulative profit with this measure:

Peak:=MAXX(FILTER(ALLSELECTED('Calendar'); 'Calendar'[Date]<=MAX('Calendar'[Date])); [Cumulative Profit$])

Lastly, I calculate the max drawdown with this measure:

Max Drawdown:=MINX(AllTrades; [Cumulative Profit$]-[Peak])

In a test table with few rows these formulas are working correctly. The problem is the performance. If a table gets a bit larger then the formulas are running forever and finally a memory error occurs. This is probably because of the multiple iterations involved.

Does anyone has a suggestion for a max drawdown calculation with better performance? Thanks in advance.

[v-haibl-msft]

@rahold

How about the result if we add an if function before do the minx calculation?

Max Drawdown = 
IF (
    [Cumulative Profit$] < [Peak],
    MINX ( AllTrades, [Cumulative Profit$] - [Peak] )
)

Best Regards,

Herbert

[rahold]

@v-haibl-msft

Thanks for the suggestion. I tried it but it doesn't make much difference.

It will probably only filter out a fraction of the calculations. Only the rows where the cumulative profit reaches a new peak will be filtered out.

[v-haibl-msft]

@rahold

When does the memory error occurs, after calculate the measure of Cumulative Profit$, Peak or Max Drawdown?

Best Regards,

Herbert

[rahold]

@v-haibl-msft

Cumulative Profit$ is calculated almost instantly, also in pivot tables. Peak takes already a bit longer in the datamodel and in pivot tables it takes a few seconds. Max Drawdown keeps running forever and eventually (can take more than 1 minute) the memory error occurs.

[v-haibl-msft]

@rahold

I haven’t found an easier way to calculate the max drawdown, all the calculation seems to be necessary here. Maybe you can try to merge these three measures into one by writing the expression for Cumulative Profit$ and Peak as variables, and reference these two variables in the final RETURN() expression. If it also doesn’t make much difference, I suggest to filter the date range in data source before you do the calculation.

I also would like to recommend this article written by Marco to you.

Best Regards,

Herbert

[rahold]

@v-haibl-msft

Writing the expressions as variables didn't make much difference.

I still have the feeling that some unneccessary iterations are taking place; I cannot believe this relatively simple calculation would need so much calculation time.

Would the use of ADDCOLUMNS and store Cumulative Profit$, Peak, and Drawdown in temporary calculated columns might be a solution?

[v-haibl-msft]

@rahold

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

[rahold]

@v-haibl-msft

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

[v-haibl-msft]

@rahold

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. 

It seems that we cannot avoid calculating Cumulative Profit$ and Peak here.

Best Regards,

Herbert

[rahold]

@v-haibl-msft

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

[v-haibl-msft]

@rahold

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

[rahold]

@v-haibl-msft

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.

[v-haibl-msft]

@rahold

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

[Greg_Deckler]

Yes, your performance issue is most likely due to the "X" functions (MAXX and MINX). Can you provide sample data and relationships?

How many total rows are we talking here when things bog down?

[rahold]

Here is my sample table:

Sample table max drawdown

And the relationships of the original tables:

Relationships

From only 1000 rows onwards the performance is already very bad.

[Greg_Deckler]

Sorry, way too much work to try to recreate this from screen shots. I'm not manually typing out 1000+ rows of information to try to recreate it.