cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular 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
Highlighted
Community Champion
Community Champion

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]

 


Owen Auger

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

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
Community Champion
Community Champion

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 🙂

 


Owen Auger

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

Connect on Twitter
Connect on LinkedIn

View solution in original post

4 REPLIES 4
Highlighted
Community Champion
Community Champion

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]

 


Owen Auger

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

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
Regular 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.

Highlighted
Community Champion
Community Champion

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 🙂

 


Owen Auger

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

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
Regular 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
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors