ValueError: closed only implemented for datetimelike and offset based windows

Hi Anonymous ,

Sorry for the late reply. Based on my testing, I could reproduce your problem.

I found that you are using rolling(5,closed='both'), which causes the error. If I deleted these two lines in the code, then it will be able to display properly in the Power BI Service.

#dffc['movingavg'] = dffc['sfc'].rolling(5,closed='both').mean()

#sns.lineplot(x=dffc['dates'],y=dffc['movingavg'])

Based on my understanding, if you want to use the closed parameter with fixed windows, you need at least version 1.2.0 of pandas. but only version 1.0.1 of pandas is supported in the Power BI Service.

So I think you need to remove the closed parameter, and it will be displayed normally.

import matplotlib.pyplot as plt
import seaborn as sns
import pandas as pd
import numpy as np

ltforecast = []
dfmindex = pandas.DataFrame ()
dfyindex = pandas.DataFrame ()
dffc = pandas.DataFrame ()
df = pandas.DataFrame({'dates': dataset["PO Creation Date"], 'spend': dataset["Commitment Value EUR"]})

df['dates'] = df['dates'].str[:10]
df['year'] = pd.DatetimeIndex(df['dates']).year
df['month'] = pd.DatetimeIndex(df['dates']).month

dfmindex['month'] = df.groupby(['month'])['spend'].mean().reset_index()['month']
dfmindex['mindex'] = df.groupby(['month'])['spend'].mean().reset_index()['spend']
dfmindex['std'] = df.groupby(['month'])['spend'].std().reset_index()['spend']
average = df['spend'].mean()
dfmindex['mindex'] = dfmindex['mindex']/average


dfyindex['year'] = df.groupby(['year'])['spend'].mean().reset_index()['year']
dfyindex['yindex'] = df.groupby(['year'])['spend'].mean().reset_index()['spend']
dfyindex['yindex'] = dfyindex['yindex']/average

slope_intercept = np.polyfit(pd.to_datetime(df['dates']).dt.strftime("%Y%m%d").astype(int),df['spend'],1)

dffc['dates'] = (pandas.date_range(start=df.iloc[-1]['dates'], periods=120, freq= 'D'))


datetonum = pd.to_datetime(dffc['dates']).dt.strftime("%Y%m%d").astype(int)

for index, row in dffc.iterrows():
    ltforecast.append((slope_intercept[0]*datetonum[index])+slope_intercept[1])

# ltforecast.append(100)



# print(dfmindex)
dffc['ltforecast'] = ltforecast

dffc['year'] = pd.DatetimeIndex(dffc['dates']).year
dffc['month'] = pd.DatetimeIndex(dffc['dates']).month

dffc1 = pd.merge(dffc,dfmindex,on ='month',how ='left')
dffc = pd.merge(dffc1,dfyindex,on ='year',how ='left')

dffc['sfc'] = (dffc['ltforecast'])*dffc['mindex']*dffc['yindex']
dffc['highpoint'] = dffc['sfc']+dffc['std']
dffc['movingavg'] = dffc['sfc'].rolling(5).mean()
prevpeaks = pandas.DataFrame()
prevpeaks['dates']=dffc['dates']-pd.Timedelta(365, unit='D')
prevpeaks = prevpeaks.drop_duplicates()

df.groupby(['dates'])['spend'].sum().reset_index()
df['dates']=df['dates'].astype('datetime64')
prevpeaks = pd.merge(prevpeaks,df,on='dates',how='left')
prevpeaks['dates'] = prevpeaks['dates']+pd.Timedelta(365, unit='D')

plt.legend(labels=["Forecast","Upper confidence line","Last year's spend"],bbox_to_anchor = (1,1),loc=2)
sns.despine()
sns.lineplot(x=dffc['dates'],y=dffc['movingavg'])
sns.lineplot(x=dffc['dates'],y=dffc['highpoint'])
if prevpeaks['spend'].iloc[1]>=0:
    sns.lineplot(x=prevpeaks['dates'],y=prevpeaks['spend'],hue=2)
plt.show()

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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