Forum Discussion

rahold's avatar
rahold
Frequent Visitor
9 years ago

Max Drawdown calculation

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.

Need Help

16 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    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?

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        9 years ago

        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.

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    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's avatar
      rahold
      Frequent Visitor
      9 years ago

      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's avatar
        v-haibl-msft
        Microsoft Employee
        9 years ago

        rahold

         

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

         

        Best Regards,

        Herbert