cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jan1 Frequent Visitor
Frequent Visitor

Calculating drawdown - running total with condition

Hello, I would like to calculate drawdown on trading account, quite easy in excel, not very easy in Power BI for me.

 

I have following data set Trade No., Net profit and I would like to calculate Balance and Drawdown. Balance is simple running total, so it is not a problem. But I have no idea how to calculate drawdown – drawdown from previous highest high.

 

Drawdown is calculated as Previous Drawdown plus Net Profit, but only if result is lower than 0 otherwise drawdown is 0.

 

Example:

 

Trade NoNet profitBalanceDrawdown
11001000
2-5050-50
3-100-50-150
4500-100
55050-50
6501000
71002000
8-100100-100
950150-50
102003500

 

 Thank you for any help.

2 ACCEPTED SOLUTIONS

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Calculating drawdown - running total with condition

Hi @jan1,

 

You could do something similar to this (alter to match your table names etc). Create the following measures:

(sample PBIX file here)

 

 

NetProfit =
SUM ( 'Trades'[Net profit] )

Balance = 
CALCULATE (
    [NetProfit],
    FILTER ( ALL ( 'Trades' ), 'Trades'[Trade No] <= MAX ( 'Trades'[Trade No] ) )
)

High Water Mark = 
MAXX (
    ADDCOLUMNS (
        FILTER ( ALL ( 'Trades' ), 'Trades'[Trade No] <= MAX ( 'Trades'[Trade No] ) ),
        "Bal", [Balance]
    ),
    [Bal]
)

Drawdown =
[Balance] - [High Water Mark]

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




OwenAuger Super Contributor
Super Contributor

Re: Calculating drawdown - running total with condition

Hi @jan1,

 

You should be able to do this using ALLEXCEPT.

 

Is everything in the SumDataStrategies table?

 

In my earlier formulas, change ALL(...) to ALLEXCEPT('SumDataStrategies', 'SumDataStrategies'[Strategy ID])

 

Something like that should work Smiley Happy

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
OwenAuger Super Contributor
Super Contributor

Re: Calculating drawdown - running total with condition

Hi @jan1,

 

You could do something similar to this (alter to match your table names etc). Create the following measures:

(sample PBIX file here)

 

 

NetProfit =
SUM ( 'Trades'[Net profit] )

Balance = 
CALCULATE (
    [NetProfit],
    FILTER ( ALL ( 'Trades' ), 'Trades'[Trade No] <= MAX ( 'Trades'[Trade No] ) )
)

High Water Mark = 
MAXX (
    ADDCOLUMNS (
        FILTER ( ALL ( 'Trades' ), 'Trades'[Trade No] <= MAX ( 'Trades'[Trade No] ) ),
        "Bal", [Balance]
    ),
    [Bal]
)

Drawdown =
[Balance] - [High Water Mark]

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




jan1 Frequent Visitor
Frequent Visitor

Re: Calculating drawdown - running total with condition

Hi @OwenAuger,

you just made my day, thank you.

 

Could you help me on one more thing, I would like to extend the dataset and add new column - Strategy ID.

And I would like to calculate Balance and Drawdown not only for entire dataset but also according to Strategy ID.

I tried to modify Balance and High Water Mark measure by extending filter using && ('SumDataStrategies'[Strategy ID] = 'SumDataStrategies'[Strategy ID]).

 

It is not working.

 

Thanks.

OwenAuger Super Contributor
Super Contributor

Re: Calculating drawdown - running total with condition

Hi @jan1,

 

You should be able to do this using ALLEXCEPT.

 

Is everything in the SumDataStrategies table?

 

In my earlier formulas, change ALL(...) to ALLEXCEPT('SumDataStrategies', 'SumDataStrategies'[Strategy ID])

 

Something like that should work Smiley Happy

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




jan1 Frequent Visitor
Frequent Visitor

Re: Calculating drawdown - running total with condition

I modified ALL to ALLEXCEPT and it works, except when Highwatermark was lower than 0.

 

So I modified Drawdown to this:

DrawdownStrategy = IF([HighWaterMarkStrategy] >= 0; [BalanceStrategy] - [HighWaterMarkStrategy]; [BalanceStrategy])

 

Now it works perfectly.

Thanks @OwenAuger

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 284 members 3,233 guests
Please welcome our newest community members: