Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I would like to calculate the final drawdown for several strategies. What I did so far yields correct results as long as the page is filtered for a single strategy, but I like to get the result for all strategies at once. The data looks like this:
There are different datetime stamps. The strategy and its profit or los are recorded. To calculate the drawdown I used the following formulas:
As soon as I select more than one the result is not correct:
How do I need to change the formulas, so that I get the correct result also when all strategies are selected?
I would like to get the Max Drawdown for specific Strategy,
Also when I select a combination of multiple Strategies\(Where I built Portfolio) for a filtered period, could be a couple days/month or 2-3 -4 years.
My Model and Relationship are
All my trades are in one table.
All trategies in 1 table.
All portfolio are in 1 table, combination of multiple Strategies
Calendar table
Definition of Max Drawdown (MDD) is the maximum observed loss from a peak to a trough of a portfolio, before a new peak is attained
Below picture is from my trading software, Green graph is Cumulative net profit, and red is Max Drawdown, I need to be able to get in BI the same MDD, so the MDDhas been reached from Peak Dec 16/21 to march 10,22.
_1_Master_Trade_Combined
TradeID | Bots.Account | Data.Exit time | P & L |
7757 | Bot_ES_MU | 2023-01-05 10:30 | -568 |
7743 | Bot_ES_MU | 2023-01-06 16:00 | 2558 |
2757 | Bot_CL_WC | 2023-01-09 10:35 | -675 |
7737 | Bot_ES_MU | 2023-01-09 13:30 | 82 |
2756 | Bot_CL_WC | 2023-01-09 16:00 | 375 |
7698 | Bot_ES_MU | 2023-01-12 15:00 | -568 |
7690 | Bot_ES_AP | 2023-01-13 10:30 | -1318 |
2751 | Bot_CL_PP | 2023-01-13 16:35 | 335 |
7686 | Bot_ES_MU | 2023-01-17 11:00 | -568 |
2749 | Bot_CL_MG | 2023-01-18 16:00 | 705 |
2750 | Bot_CL_WC | 2023-01-18 16:00 | 715 |
7671 | Bot_ES_MU | 2023-01-18 16:00 | 1895 |
7669 | Bot_ES_MU | 2023-01-19 13:30 | 45 |
7659 | Bot_ES_MU | 2023-01-20 16:00 | 2432 |
2747 | Bot_CL_PP | 2023-01-20 16:35 | 165 |
2746 | Bot_CL_WC | 2023-01-23 10:10 | -665 |
7635 | Bot_ES_MU | 2023-01-24 10:30 | -568 |
7623 | Bot_ES_MU | 2023-01-25 10:30 | -568 |
7628 | Bot_ES_AP | 2023-01-25 11:30 | 8 |
2744 | Bot_CL_MG | 2023-01-27 14:30 | 205 |
2745 | Bot_CL_WC | 2023-01-27 14:30 | 225 |
2742 | Bot_CL_PP | 2023-01-27 16:35 | -225 |
2741 | Bot_CL_WC | 2023-01-30 11:40 | -175 |
7604 | Bot_ES_AP | 2023-01-30 16:00 | 808 |
7597 | Bot_ES_MU | 2023-01-30 16:00 | 745 |
7588 | Bot_ES_MU | 2023-01-31 16:00 | 995 |
Portfolio
Portfolio | Bots.Account |
Port #1 | Bot_ES_MU |
Bot_ES_MU | |
Bot_CL_WC | |
Bot_CL_PP | |
Port#2 | Bot_CL_WC |
Bot_ES_MU | |
Bot_ES_AP | |
Port#3 | Bot_CL_PP |
Bot_ES_MU | |
Bot_CL_MG | |
Bot_ES_AP |
I need to add Column of MDD first (and be able to generate a bar graph), the previous suggestion in this thread is not reflecting the MDD as I described above.
Thanks
adding my DAX
CumProfit = SUMX(_1_Master_Trade_Combined, _1_Master_Trade_Combined[P & L])
CumProfitByDateTime = CALCULATE([CumProfit], FILTER(ALLSELECTED('_1_Master_Trade_Combined'), '_1_Master_Trade_Combined'[Data.Exit time] <= MAX('_1_Master_Trade_Combined'[Data.Exit time])))
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])
DrawDownByDateTime = calculate(_1_Master_Trade_Combined[CumProfitByDateTime] - _1_Master_Trade_Combined[PeakByDateTime], all (_1_Master_Trade_Combined[Bots.Account]))
High Water Mark =
MAXX (
ADDCOLUMNS (
FILTER ( ALL ( '_1_Master_Trade_Combined' ), '_1_Master_Trade_Combined'[TradeID] <= MAX ( '_1_Master_Trade_Combined'[TradeID] ) ),
"Bal", [CumProfitByDateTime]
),
[Bal]
)
Hello,
The above works DAX however, it is for every day
Instead to get Drawdonw per day, I would like to find MaxDrawdown for a period I would select. Not per trade or per Day!
1-if only one Strategy Selected, the Drawdown for the Period selected,
2- Multiple stategy selected to get the Max Drawdown for the period selected, (the Max Drawdown for the selected Strategy, as exemple a portfolio of multiple strategy)
DrawDownByDateTime = calculate(table[CumProfitByDateTime] - table[PeakByDateTime],all(table[stragtegy])
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
Hi, @mh2587 , Thanks, but this gives the total drawdown over all strategies (also nice), but I wanted to have the final drawdown of each single strategy in one table/ visual. It should look like this:
Then use sumx function
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |