Help with DAX Code to calculate "Maximum Drawdown" of Cumulative Return over time

[sinsai]

Could you provide your DAX code or suggestions for calculating the 'Maximum Drawdown' of Cumulative Returns over time? I can easily perform this calculation in MS Excel (ColumnD), as an example, =MIN((C3-MAX($C$3:C3))/MAX($C$3:C3),0).

• Dataset provided below. ColumnA and B are provided
• ColumnC and D will need to be calculated dynamically. Examples below,
  • ColumnC: C10 = C9*(1+B10)
  • ColumnD: D10 = MIN( (C10 - MAX($C$3:C10) ) / MAX($C$3:C10), 0 )
• Answer: MIN(ColumnD) will need to be returned to Power BI

I've tried asking 'ChatGPT'. The sample codes do not solve for the dynamic rolling window dataset, ie (C3 - MAX($C$3:C3)) and MAX($C$3:C3).

I can't attached the Excel file (as example).

Below is the code I've been working on,

------------------------------------

Maximum_Drawdown =

VAR PortIdSelected = SELECTCOLUMNS(port_return, port_return[Port_Id])

VAR MaxDate = MAXX(SELECTCOLUMNS(date_table, date_table[Date]), date_table[Date])

VAR CumulativeValue =

    PRODUCTX(

        FILTER(

            port_return,

            port_return[Date] <= MaxDate && port_return[Port_Id] = PortIdSelected

        ),

        1 + port_return[Total_Return_Value]

    ) - 1

VAR MaxValueUntilNow =

    MAXX(

        port_return,

        port_return[Cumulative_Total_Return_Value],

        TRUE

    )

VAR Drawdown = (port_return[Cumulative_Total_Return_Value] - MaxValueUntilNow) / MaxValueUntilNow

VAR MaxDraw =

    MINX(

        ALL(port_return[Cumulative_Total_Return_Value]),

        Drawdown

    )

RETURN MaxDraw

------------------------------------

MS Excel example,

Date	Rate of Return	Cumulative Return	Max DrawDown Calc
7/31/2006	0.00989919167052200000	1.00989919167052000000	0.00000000000000000000
8/31/2006	0.02553251190174240000	1.03568445480141000000	0.00000000000000000000
9/29/2006	0.01527200845834750000	1.05150143655532000000	0.00000000000000000000
10/31/2006	0.04566924156514870000	1.09952270966746000000	0.00000000000000000000
11/30/2006	0.03424632374726940000	1.13717732035021000000	0.00000000000000000000
12/29/2006	0.02958188287003780000	1.17081716664327000000	0.00000000000000000000
1/31/2007	0.01718309699541430000	1.19093543158160000000	0.00000000000000000000
2/28/2007	-0.00365343047963085000	1.18658443177659000000	-0.00365343047963093000
3/30/2007	0.02614030318011280000	1.21760210857203000000	0.00000000000000000000
4/30/2007	0.05339327092422130000	1.28261386783292000000	0.00000000000000000000
5/31/2007	0.03445125567586960000	1.32680152612705000000	0.00000000000000000000
6/29/2007	-0.00278343434495243000	1.32310846119029000000	-0.00278343434495238000
7/31/2007	-0.02346846307500970000	1.29205713912461000000	-0.02618657449381580000
8/31/2007	-0.00851522219555290000	1.28105498549562000000	-0.03447881218901340000
9/28/2007	0.03798022683821260000	1.32970974443696000000	0.00000000000000000000
10/31/2007	0.03098872691089060000	1.37091575657807000000	0.00000000000000000000
11/30/2007	-0.05358450251909400000	1.29745591776625000000	-0.05358450251909390000
12/31/2007	-0.01180931728166990000	1.28213384917436000000	-0.06476102340913540000
1/31/2008	-0.06795320645159520000	1.19500874302284000000	-0.12831351066699300000
2/29/2008	-0.00501197322008429000	1.18901939120504000000	-0.13268238000783900000
3/31/2008	-0.01388957888876040000	1.17250441257063000000	-0.14472905651233200000
4/30/2008	0.05467128152369050000	1.23660673139805000000	-0.09797029798188570000
5/30/2008	0.01485475443028620000	1.25497622071981000000	-0.08457086826958230000
6/30/2008	-0.06589997399312560000	1.17227332041238000000	-0.14489762424316600000
7/31/2008	-0.03904383396732510000	1.12650327552588000000	-0.17828409942728200000
8/29/2008	-0.02685550498697150000	1.09625046119215000000	-0.20035169489298600000
9/30/2008	-0.13699995914422700000	0.94606419279698800000	-0.30990348002239700000
10/31/2008	-0.20259638180579400000	0.75439501038029900000	-0.44971453806663000000
11/28/2008	-0.06698987071864250000	0.70385818616413400000	-0.48657809001989400000
12/31/2008	0.03329070244551600000	0.72729011960356400000	-0.46948591398573800000
1/30/2009	-0.08386093119419070000	0.66629889292527500000	-0.51397531925052900000
2/27/2009	-0.09068387896306260000	0.60587632476601700000	-0.55804992257267500000
3/31/2009	0.08538608825626960000	0.65760973410487200000	-0.52031353425660100000
4/30/2009	0.12748727528134400000	0.74144660730439200000	-0.45915961374963700000
5/29/2009	0.10240586362709900000	0.81737508745878100000	-0.40377438691125500000
6/30/2009	-0.00416811061751043000	0.81396817767825500000	-0.40625952121960100000
7/31/2009	0.08932057123531310000	0.88667228027584400000	-0.35322628248940800000
8/31/2009	0.03885243675661520000	0.92112165896910500000	-0.32809754753398700000
9/30/2009	0.04883239176948880000	0.96610223268724500000	-0.29528694374428600000
10/30/2009	-0.01769382485022260000	0.94900818899466800000	-0.30775601313134000000
11/30/2009	0.04073826542932960000	0.98766913649254000000	-0.27955519385242700000
12/31/2009	0.02431672522642780000	1.01168601549925000000	-0.26203633546052900000
1/29/2010	-0.04339957754780130000	0.96777926981556700000	-0.29406364674717000000
2/26/2010	0.01306243567587020000	0.98042082427597300000	-0.28484239854154600000
3/31/2010	0.06763593992027260000	1.04673250824329000000	-0.23647204197577600000
4/30/2010	0.00655895313984556000	1.05359797771481000000	-0.23146409787813300000
5/31/2010	-0.09452371058016410000	0.95400798740144700000	-0.30410896306076600000
6/30/2010	-0.03191686497128030000	0.92355904328603200000	-0.32631962332148000000
7/30/2010	0.08184993306454460000	0.99915228916014900000	-0.27117892958344600000
8/31/2010	-0.03571166698657190000	0.96347089534079100000	-0.29720634494295800000
9/30/2010	0.09994811493226190000	1.05976799512220000000	-0.22696344393365300000
10/29/2010	0.03690789988955130000	1.09888180619232000000	-0.19843228811139200000
11/30/2010	-0.01994400265039880000	1.07696570453715000000	-0.21441875668177300000
12/31/2010	0.07608213159872880000	1.15890355099706000000	-0.15465006114614200000
1/31/2011	0.01458507783347370000	1.17580624948984000000	-0.14232056649143700000
2/28/2011	0.02803629330666090000	1.20877149837234000000	-0.11827441433050000000
3/31/2011	0.00214373422492198000	1.21136278320351000000	-0.11638422901551100000
4/29/2011	0.04409744796924350000	1.26478079050771000000	-0.07741902852971930000
5/31/2011	-0.02039059703787340000	1.23899115506722000000	-0.09623100535377950000
6/30/2011	-0.01872063468084510000	1.21579645428041000000	-0.11315013453842600000
7/29/2011	-0.01886092709471210000	1.19286540599422000000	-0.12987694519485200000
8/31/2011	-0.07476597565952090000	1.10367966008457000000	-0.19493254433120200000
9/30/2011	-0.09711493727741480000	0.99649587912110000000	-0.27311661979256500000
10/31/2011	0.11042749192001700000	1.10653641976108000000	-0.19284871119791400000
11/30/2011	-0.03360199475397400000	1.06935458878918000000	-0.21997060456990500000
12/30/2011	-0.00535697943862512000	1.06362607824444000000	-0.22414920600274700000

Desireable outcome, return the value in column Max Draw Down Calc (-0.55804992257267500000) for Date (2/27/2009)

Please assist if you can.

[v-pgoloju]

Hi @sinsai,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @sinsai,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks & Regards,

Prasanna kumar

[v-pgoloju · Accepted solution]

Hi @sinsai 

Thank you for reaching out to the Microsoft Fabric Forum Community.

Also, thanks to @lbendlin   for the prompt and helpful response.

You can try using the following DAX calculated columns:

CumulativeReturn =
VAR CurrentDate = drawdown[Date]
RETURN
CALCULATE (
PRODUCTX (
FILTER (
ALL (drawdown),
drawdown[Date] <= CurrentDate
),
1 + drawdown[RateOfReturn]
)
)

MaxCumulativeSoFar =
VAR CurrentDate = drawdown[Date]
RETURN
CALCULATE (
MAX (drawdown[CumulativeReturn]),
FILTER (
ALL (drawdown),
drawdown[Date] <= CurrentDate
)
)

and create measure:

Drawdown =
DIVIDE (
drawdown[CumulativeReturn] - drawdown[MaxCumulativeSoFar],
drawdown[MaxCumulativeSoFar],
0
)

I've attempted to reproduce the issue using the raw data. Kindly let me know if this solution works for you. If the issue still persists, please feel free to share more details so we can further investigate and help resolve it.

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thank you!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...