cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jadson
Regular Visitor

Rolling Drawdown

Hi, I'm new in Power Bi and I have one problem. I have the following dataset:

Jadson_0-1632515199713.png

I would like to plot a drawdown for each Id_Fund over time. I want this graph to vary as i change the period and the Id_Fund.

For example, in following graph done in python, i have the drawdown graph, but static. I would like this graph changes with date slicer. 

Jadson_1-1632515791343.png

My logic for solving this problem is as follow: I have to create a rolling max, that is, for each day I calculate the max value since my initial date (initial date in date slicer). From that, I divide the current value by the max value.

I build this logic in python, but I cant do it in power bi and create a dynamic graph. Below is the code in python.

# Data: create a column for each Id_fund (In power bi, I think this is not necessary)
cota = NAV_SHARE.pivot(index="Dt_FundNavDate",columns="Id_Fund",values="Vlr_FundNavValue")
cota.reset_index("Dt_FundNavDate",inplace=True)
cota["Dt_FundNavDate"] = pd.to_datetime(retorno["Dt_FundNavDate"],format="%Y-%m-%d")
# Rolling max and calculate de drawdown
RollMax = retorno.cota(1000,min_periods=1).max() # The number 1000 is arbitrary.
dailydd = cota/RollMax -1

Thanks,

Jadson

1 REPLY 1
Ashish_Mathur
Super User
Super User

Hi,

Could you sow the expected result in a Table format.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.