Issue with Calculating Drawdown with Filters in Power BI

[aaftab42]

Hi Community Members,

I'm currently working on a Power BI project and could use some guidance on optimizing my drawdown calculations. Here's a summary of my approach and the challenge I'm facing:

Current Approach

1. Summarized Table

Created a summarized table to reduce dataset size:

DailySummary = 
SUMMARIZE(
    four_strategy,
    four_strategy[Entry_Date],
    "DailySummaryTotalProfit", [AdjustedTotalProfit]
)

2. Cumulative Profit Measure

Calculated cumulative profit:

DailyCumulativeProfit = 
CALCULATE(
    SUMX(
        FILTER(
            ALL(DailySummary),
            DailySummary[Entry_Date] <= MAX(DailySummary[Entry_Date])
        ),
        [AdjustedTotalProfit]
    )
)

3. Running Max Profit

Created a measure for running max profit:

RunningMaxProfit = 
CALCULATE(
    MAXX(
        FILTER(
            ALL(DailySummary),
            DailySummary[Entry_Date] <= MAX(DailySummary[Entry_Date])
        ),
        [DailyCumulativeProfit]
    )
)

4. Daily Drawdown

Finally, calculated the daily drawdown:

DailyDrawdown = 
[DailyCumulativeProfit] - [RunningMaxProfit]

Issue

The current approach works well without filters. However, when applying filters like specific years or days, the results are invalid. Using ALLSELECTED works for granular filters but leads to query exceeded errors when more filters are applied (e.g., filtering more than 2 years out of 5 years of data).

 

I'm looking for a solution to calculate the drawdown accurately while handling filters without exceeding memory limits. Any suggestions or best practices on optimizing these calculations would be greatly appreciated.

Thank you in advance for your help!

[manoj_kumar95]

I am using the same formulas for cummulative sum and max but the cummulative max is not working i dont know the reason. it is taking the same values through out the data though the data is changed in asecnding or descding ordrer. Please help me with that.

I am attaching the project file, the screenshots and the formulas below.

Link:- https://drive.google.com/file/d/19TmkeaslWT-KueSMjnDZO3QVjVOl0ApP/view?usp=sharing

DailyCumulativeProfit =

CALCULATE(

    SUMX(

        FILTER(

            ALL(trade_logs),

            trade_logs[date] <= MAX(trade_logs[date]

            )

        ),

        trade_logs[pnl]

    )

)

RunningMaxPnL =

CALCULATE(

    MAXX(

        FILTER(

            ALL(trade_logs),

           trade_logs[date] <= MAX(trade_logs[date]

            )

        ),

        [DailyCumulativeProfit]

    )

)

@aaftab42 @TomMartens 

[Ashish_Excel]

Hi,

Show the expected result very clearly.

[manoj_kumar95]

I have attached the screenshots and pbix file. I need to get the correct running max. But y using the above formula i am not getting.

In this example see the dailycummulativeprofit and RunningMaxProfit 

1st image when not sorted

2nd image when dailycummulative profit sorted ascending

3rd image when dailycummulativeprofit column sorted descending 

in all these three cases the running max is changing dynamically but in my case it isnt changing it is giving a constant value all over the rows.

I am again attaching my power bi file once please refer to it and please help me out.

Link:- https://drive.google.com/file/d/19TmkeaslWT-KueSMjnDZO3QVjVOl0ApP/view?usp=sharing

when not sorted

When cummulativeprofit sorted ascending

When cumulativeprofit column sorted descending

[Ashish_Excel]

Hi,

You have still not answered my previous question - you do not show the expected result at all.  Anyways, based on whatever my understanding is, download the PBI file from here.

[TomMartens]

Hey @aaftab42 ,

please provide a pbix that contains sample data, but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or Dropbox and share the link. If you are using spreadsheet to create the sample data instead of the manual import method, share the spreadsheet as well.

Do not forget to describe the expected result based on the sample data you provide.

Regards,

Tom

[aaftab42]

Hi Tom,

I hope this message finds you well.

I have encountered an issue with the calculation of drawdown in my Power BI report, and I would appreciate any guidance or advice you could offer.

Details of the Issue:

• Data Structure: I have three files:
  • four_strategy: This is the fact table.
  • indices: This is a category table.
  • strategy: This is a sub-category table.
• Measures Involved:
  • cumulativeprofit
  • running max profit
  • dailydrawdown

Problems:

• If the first day shows a negative value in profit and loss, it does not mark it as a drawdown.
• Applying filters to specific years or days results in incorrect calculations due to the use of the ALL function, which does not adhere to the filters. Using ALLSELECTED to fix this issue leads to memory exceed errors.

Objective:

I would like to have an accurate drawdown calculation in a line chart that reflects the drawdown under different filters, similar to how the total profit is displayed.

Files and Data:

I have created a sample .pbix file that includes the tables, relationships, calculated columns, and measures. Additionally, I am providing spreadsheets with sample data to better illustrate the issue. The files are available at the following link:

Google Drive Link

Expected Result:

The goal is to accurately calculate and display the drawdown in a line chart, while correctly responding to different filters applied to the data, such as specific years or days, without causing memory issues.

Thank you in advance for your support and assistance.

Best regards,
Aaftab Ahmad