Help is appreciated, How to calculate the MAX drawdown for several trading strategies

[Patrader]

Hi, I would like to calculate the MAX drawdown for several trading strategies. What I get correct so far is Cumulative Profit, CumProfitByDate.

I have Peak and Valley "Kind of" worked out but I need to find the Max Drawdown, (the maximum observed loss from a peak to a trough of a portfolio, before a new peak is attained).

CumProfit = SUMX(_1_Master_Trade_Combined, _1_Master_Trade_Combined[P & L])                                         (Works)

CumProfitByDateTime = CALCULATE([CumProfit], FILTER(ALLSELECTED('_1_Master_Trade_Combined'), '_1_Master_Trade_Combined'[Data.Exit time] <= MAX('_1_Master_Trade_Combined'[Data.Exit time])))               (Works)

PeakByDateTime = MAXX(FILTER(ALLSELECTED('_1_Master_Trade_Combined') , '_1_Master_Trade_Combined'[Data.Exit time].[Date] <= MAX('_1_Master_Trade_Combined'[Data.Exit time].[Date])), [CumProfitByDateTime])                                    

*** PeakByDateTime ******  (Partially works, only giving me the highest peak, I need the peak  beofre the valley where the difference will be the max)

ValleyByDateTime = MINX(FILTER(ALLSELECTED('_1_Master_Trade_Combined') , '_1_Master_Trade_Combined'[Data.Exit time].[Date] >= MIN('_1_Master_Trade_Combined'[Data.Exit time].[Date])), [CumProfitByDateTime])

*** ValleyByDateTime ******  (Partially works, only giving me the lowest valley, I need the valley after peak where the difference will be the max) 

Max_DD_Peak_Valley = [PeakByDateTime]-[ValleyByDateTime]                       ****No working, only giving the Delta from highest PEAK to Lowest Valley, I need the maximum observed loss from a peak to a trough of a portfolio, before a new peak is attained).

My Trades table:

Result so far

Max DD = the maximum observed loss from a peak to a trough of a portfolio, before a new peak is attained). see below picture

[v-jianboli-msft · Accepted solution]

Hi @Patrader ,

Please try:

VAR _a =
    SUMMARIZE (
        '_1_Master_Trade_Combined',
        _1_Master_Trade_Combined[Data.Exit time],
        "CumProfitByDate", [CumProfitByDateTime]
    )
RETURN
    MAXX (
        FILTER (
            _a,
            MINX (
                FILTER (
                    _a,
                    [Data.Exit time] > EARLIER ( _1_Master_Trade_Combined[Data.Exit time] )
                        && [Data.Exit time]
                            < IF (
                                ISBLANK (
                                    MINX (
                                        FILTER (
                                            _a,
                                            [Data.Exit time] > EARLIER ( _1_Master_Trade_Combined[Data.Exit time] )
                                                && [CumProfitByDate] > EARLIER ( [CumProfitByDate] )
                                        ),
                                        [Data.Exit time]
                                    )
                                ),
                                MAXX ( ALL ( _1_Master_Trade_Combined[Data.Exit time] ), [Data.Exit time] ),
                                MINX (
                                    FILTER (
                                        _a,
                                        [Data.Exit time] > EARLIER ( _1_Master_Trade_Combined[Data.Exit time] )
                                            && [CumProfitByDate] > EARLIER ( [CumProfitByDate] )
                                    ),
                                    [Data.Exit time]
                                )
                            )
                ),
                [CumProfitByDate]
            )
                <> BLANK ()
        ),
        [CumProfitByDate]
            - MINX (
                FILTER (
                    _a,
                    [Data.Exit time] > EARLIER ( _1_Master_Trade_Combined[Data.Exit time] )
                        && [Data.Exit time]
                            < IF (
                                ISBLANK (
                                    MINX (
                                        FILTER (
                                            _a,
                                            [Data.Exit time] > EARLIER ( _1_Master_Trade_Combined[Data.Exit time] )
                                                && [CumProfitByDate] > EARLIER ( [CumProfitByDate] )
                                        ),
                                        [Data.Exit time]
                                    )
                                ),
                                MAXX ( ALL ( _1_Master_Trade_Combined[Data.Exit time] ), [Data.Exit time] ),
                                MINX (
                                    FILTER (
                                        _a,
                                        [Data.Exit time] > EARLIER ( _1_Master_Trade_Combined[Data.Exit time] )
                                            && [CumProfitByDate] > EARLIER ( [CumProfitByDate] )
                                    ),
                                    [Data.Exit time]
                                )
                            )
                ),
                [CumProfitByDate]
            )
    )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Patrader]

@v-jianboli-msft ,

Thank so much! you are a genius! This is what I wat looking for, I spent so much time and when I gave up, you save my life.... Very fast, accurate.

Thanks again, 

[v-jianboli-msft]

Hi @Patrader ,

BAsed on your description, I have creaed a simple sample:

Please try:

Measure = 
VAR _a =
    SUMMARIZE (
        ALL ( '_1_Master_Trade_Combined' ),
        _1_Master_Trade_Combined[Data.Exit time],
        "CumProfitByDateTime",
            CALCULATE (
                SUM ( _1_Master_Trade_Combined[P & L] ),
                FILTER (
                    ALL ( '_1_Master_Trade_Combined' ),
                    [Data.Exit time] <= MAX ( '_1_Master_Trade_Combined'[Data.Exit time] )
                )
            )
    )
VAR _b =
    ADDCOLUMNS (
        _a,
        "Tag",
            IF (
                [CumProfitByDateTime]
                    >= CALCULATE (
                        SUM ( _1_Master_Trade_Combined[P & L] ),
                        FILTER (
                            ALL ( '_1_Master_Trade_Combined' ),
                            [Data.Exit time] < EARLIER ( '_1_Master_Trade_Combined'[Data.Exit time] )
                        )
                    ),
                "Peak",
                "Valley"
            )
    )
VAR _c =
    ADDCOLUMNS (
        _b,
        "DrawDown",
            IF (
                [Tag] = "Peak"
                    && NOT (
                        ISBLANK (
                            MINX (
                                FILTER (
                                    _b,
                                    [Data.Exit time] > EARLIER ( _1_Master_Trade_Combined[Data.Exit time] )
                                        && [Data.Exit time]
                                            < MINX (
                                                FILTER (
                                                    _b,
                                                    [Data.Exit time] > EARLIER ( _1_Master_Trade_Combined[Data.Exit time] )
                                                        && [Tag] = "Peak"
                                                ),
                                                [Data.Exit time]
                                            )
                                ),
                                [CumProfitByDateTime]
                            )
                        )
                    ),
                [CumProfitByDateTime]
                    - MINX (
                        FILTER (
                            _b,
                            [Data.Exit time] > EARLIER ( _1_Master_Trade_Combined[Data.Exit time] )
                                && [Data.Exit time]
                                    < MINX (
                                        FILTER (
                                            _b,
                                            [Data.Exit time] > EARLIER ( _1_Master_Trade_Combined[Data.Exit time] )
                                                && [Tag] = "Peak"
                                        ),
                                        [Data.Exit time]
                                    )
                        ),
                        [CumProfitByDateTime]
                    )
            )
    )
RETURN
    MAXX ( _c, [DrawDown] )

 Final Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Patrader]

Hi @v-jianboli-msft 

Great for the sample, I try to implement it in my system, very long then got error of memoy... i have 64gb RAM...

Anyway to make it to pull less memory? intead addcolumn, where I guest it is taking memory away

[Patrader]

Hello again @v-jianboli-msft ,

I worked  around your amazing works you provider to me!

I Cleaned my datas to the minimum (Still have 25,000 lines), disconnected all Relationship, to se if I can some results now due Memory errors.

I need extra Filter as I would like to select some "Bots.accounts" and for a period "Data.Exit.Date", the Filter are all from the main table_1_Master_Trade_Combined, no relationship filter.

PS: I tried without success!

Thank you so much!

[Patrader]

Hi @v-jianboli-msft , 

Here is my pbix.

see previous message, I forgot to tag you, sorry!

if you have issue to get access, message me directly, I will send give you access if it doesnt work.

https://www.dropbox.com/s/3m3pbwxtcdfe3l7/MaxDD_Shared.pbix?dl=0

To avoid the back and forth messaging within the forum, you can get in touch in private message

Thanks you so much

[v-jianboli-msft]

Hi @Patrader ,

What are the specific definitions of peak and trough? Is it the change in the high and low values of each segment? You mentioned that you want the peak to be the peak before the valley and the valley to be the valley after the peak, which one should I calculate first? From your description, these two don't seem to be specifically defined and I'm not sure how to calculate them.

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Patrader]

Hi,

Thank for your reply,

Im not sure how I can send you directly my pbix? or I can only in the forum?

Peak,(highs) and trough(lows), We have waves, many high and lows,

"Drawdown:- Reduction in capital from a peak to trough

Max Drawdown= Maximun of all the Drawdown"

[Patrader]

 @OwenAuger, @mh2587,

Hi,

You have been very helpful in some similar question.