Forum Discussion

Power_BI_Help's avatar
Power_BI_Help
Icon for Helper I rankHelper I
6 years ago
Solved

Calculated Column: Rolling Maximum by Label (or high water mark)

Hello Community, 

I have been calculating Drawdown = Current Value / Rolling Maximum (or high water mark) for each different label. I have this DAX formula working, however, it is very laggy to process that I am looking to optimize. 

Portfolio - Drawdown (%) Calculated =
 
Formula 1:
Var Drawdown =
DIVIDE(
[Portfolio - Equity Total EOD Day Change ($) Cumulative with Initial] - [Portfolio - Rolling Maximum],
[Portfolio - Rolling Maximum]
)
Return
// If the value is greater than - the drawdown is technically 0
IF(
Drawdown >= 0,
0,
Drawdown
)



I was thinking of adding a specific column for Rolling Maximum would remove the calculation from the DAX. However, I am finding that the same logic filters can not be applied to a calculated column as a DAX formula. 

 

When I try a formula such as what is listed below, it simply populates the max value in that column and does not refer to the date (or an index that I created) to keep proper filtering. Is there a trick to calculated columns that I am missing?

 
Formula 2:
Portfolio - Rolling Maximum =
CALCULATE( // Searches the maximum value in the total EOD row filtered by the maximum date when using time series
MAXX(
'Daily Ledgers',
'Daily Ledgers'[Portfolio - Equity Total EOD ($)]
),
FILTER(
ALLSELECTED('Date'),
'Date'[Date] <= MAX('Date'[Date]
)
)
)

 

Thanks for any hints in advance

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    HI  Power_BI_Help 

    For your case, i would suggest you use Date hierarchy in the x-axis, since there are to many day in the axis, so the calculation will be very slow, so you'd better just use Year-quarter-Month in the visual, do not use dim day any more.

     

    here is sample pbix file, please try it.

     

    Regards,

    Lin

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Can you provide more context behing Laggy?  

     

    is the measure taking to long to calculate when you select slicers?

    are you using this in a measure or calculated column? 

     

    need a bit more info to try and help 

    • Power_BI_Help's avatar
      Power_BI_Help
      Icon for Helper I rankHelper I
      6 years ago

      amitchandak That seems to grab the max value within the day across the different IDs but does not create a rolling max per ID.

      What I was looking for:
      ID // Date // Value // Max

      1 // 1.1 // 40 // 40

      1 // 1.2 // 30 // 40

      1 // 1.3 // 50 // 50

      2 ... 

       

      Anonymous here is a link to the file that replicates what I am talking about. My charts on the actual file take ~40 - 60 seconds to load and I am planning to scale the file much more significantly (my file has about 5 IDs). And yes, when changing different factors and parameters needing to reload (or drilling down) takes a while. Ultimately, looking for a less computationally intensive way to calculate this. I was thinking that if I created a calculated column that had preprocessed the rolling maximum value it would cut down on load time significantly.

       

       https://1drv.ms/u/s!AqA0zdOdqkphhU41gG8baO20fZTq?e=2K4VPX

       

       

      Thanks for the help so far!

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        Power_BI_Help , it can if the ID is present in the visual table

         

        Else put in calculate and force Id using values(Table[ID])