Need Help with Calculating Drawdown in Power BI: Memory Limit Exceeded Error

[aaftab42]

Hello,

I am currently generating reports for backtest data in Power BI, and I am trying to calculate the drawdown for my trading strategies. However, I'm encountering a "memory limit exceeded" error due to the complex calculations involved.

To achieve my goal, I've created three DAX measures:

1. Cumulative Profit:
   TotalCumulativeProfit = CALCULATE([TotalProfit], FILTER(ALLSELECTED(Report), Report[Entry Date] <= MAX(Report[Entry Date])))
2. Max Cumulative Profit:
   MaxCumulativeProfit = CALCULATE(MAXX(ALL(Report), [TotalCumulativeProfit]), FILTER(ALL(Report[Entry Date]), Report[Entry Date] <= MAX(Report[Entry Date])))
3. Drawdown:
   Drawdown = VAR MaxProfit = [MaxCumulativeProfit] VAR CurrentProfit = [TotalCumulativeProfit] RETURN CurrentProfit - MaxProfit

I have a single table with approximately 8,000 rows. While the calculations work well in Excel, I'm having trouble when different strategies are selected in Power BI. Sometimes, the matrix displays the drawdown correctly for monthly views, but it fails for weekly and daily views.

I would appreciate any guidance on how to resolve the memory limit issue and achieve the desired drawdown calculations in Power BI. Thank you for your assistance!

[aaftab42 · Accepted solution]

Hey I have found the solution, Thank you so much for your time guys,
I had to summarize the new table by limited number of necessary details on day and weekly level and it calculated the drawdown easily.

[FarhanJeelani]

To address the memory limit issue in Power BI while calculating drawdown, especially with varying time periods, here are a few optimization strategies:

1. Use Variables to Minimize Redundant Calculations
Storing intermediate values in variables can help Power BI process DAX more efficiently, as it avoids recalculating values repeatedly.

 2. Simplify the `MaxCumulativeProfit` Calculation
The `MaxCumulativeProfit` measure can be resource-intensive because it recalculates the maximum cumulative profit for each date. By limiting the calculation to only relevant dates, you can reduce memory usage.

Try rewriting your measures as follows:

 Cumulative Profit:

TotalCumulativeProfit = 
VAR CurrentDate = MAX(Report[Entry Date])
RETURN
    CALCULATE(
        [TotalProfit], 
        FILTER(
            ALLSELECTED(Report), 
            Report[Entry Date] <= CurrentDate
        )
    )

Max Cumulative Profit (Optimized):

MaxCumulativeProfit = 
VAR CurrentDate = MAX(Report[Entry Date])
RETURN
    CALCULATE(
        MAXX(
            FILTER(ALL(Report), Report[Entry Date] <= CurrentDate),
            [TotalCumulativeProfit]
        )
    )

3. Alternative Drawdown Calculation Using Running Max
You could also simplify `MaxCumulativeProfit` by using a "running maximum" approach to avoid recalculating over the entire date range. This can help reduce memory load:

Drawdown = 
VAR RunningMaxProfit = 
    CALCULATE(
        MAX([TotalCumulativeProfit]),
        FILTER(
            ALL(Report),
            Report[Entry Date] <= MAX(Report[Entry Date])
        )
    )
RETURN 
    [TotalCumulativeProfit] - RunningMaxProfit

4. Apply Calculation Groups for Time Periods
If you’re calculating drawdown across multiple time periods (daily, weekly, monthly), consider using Calculation Groups in Power BI’s Tabular Editor (if available). Calculation Groups allow you to apply dynamic time-based calculations without duplicating DAX measures for each period, saving memory.

5. Check Data Granularity and Aggregation Levels
If your dataset allows, try reducing data granularity or pre-aggregating data at a higher level (e.g., monthly instead of daily) for the drawdown calculation. Power BI handles higher levels of aggregation more efficiently, so you could provide users with options for monthly or weekly drawdowns instead of daily.

6. Adjust Storage Mode
For large, complex calculations, consider using **DirectQuery** mode if your data source can support it, or optimizing your data model by reducing unnecessary columns or rows to lighten memory usage.

Try implementing these optimizations and see if the memory usage improves. Let me know if you need further guidance on any of the steps!

[aaftab42]

Hi @FarhanJeelani 

Thank you for the details explanation, I have tried using variable but it was not working,
I am not very seasoned in power bi, few of points mentioned by you are gone out of my mind. In excel it was super easy to have drawdown calculation, I think i am still doing something wrong. How can i share the sample file?

[vojtechsima]

Hello, @aaftab42 ,

generally, you can do cumulative count with date table a fact table just in general formula like this:

cumulative = 
CALCULATE(
    COUNTROWS('Table'),
    'Calendar'[Date] <= MAX('Calendar'[Date])
)

 This avoids multiple iterations that you have in your formulas.

Another point could be that you have Many To Many relationships that will further slow down the performance.

Feel free to drop sample file of your solution so I can investigate directly on it.

[aaftab42]

Hi @vojtechsima ,
I don't have many to many relationship in my data model. I have tried using date table instead of fact table, but the issue remain the same. I am new to this forum, how to send the the sample file?

[vojtechsima]

@aaftab42  hello, just perhaps upload it to Onedrive or Google drive and send the link, you cannot directly attach files unless you're superuser.