cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Power_BI_Help
Helper I
Helper I

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

1 ACCEPTED SOLUTION

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Dnerada
Post Patron
Post Patron

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 

@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 ... 

 

@Dnerada 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!

@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])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@amitchandak 

The function seems to grab the last non blank value (i.e. the same day) not the maximum value of all the dates leading up to that day. I attached a photo with the formula you've stated and the incorrect graph it creates when compared to the correct chart on the left. The calculated column does the same. It is not an all time high value for the column up to that date.... 


https://imgur.com/a/pZmebtn

 

Ideas?
I appreciate the comments.

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

D'oh, can't believe that was all I needed to do. So much faster and more scalable, thanks.

amitchandak
Super User
Super User

@Power_BI_Help , Not clear. Can you share sample data and sample output in table format?

 

You can try like this

lastnonblankvalue(Date'[Date], max('Daily Ledgers'[Portfolio - Equity Total EOD ($)]))

 

refer:https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!