Forum Discussion

Jadson's avatar
Jadson
Regular Visitor
4 years ago

Rolling Drawdown

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

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. 

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