Milestone Trend Analysis Using Python

[kala2]

Following the example of the implementation on the github repo:
https://github.com/barakb32/PBI-MTA

And the topics:
https://community.powerbi.com/t5/Desktop/Create-Milestone-Trend-Analysis-MTA-in-Power-BI/m-p/191509
https://community.powerbi.com/t5/Developer/Milestone-Trend-Analysis/m-p/1366360

I want to create a milestone trend analysis like the image below:

My dataset is in the following format: 

And my python script: 

# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script: 

# dataset = pandas.DataFrame(M Actual Value, M, M Plan Value)
# dataset = dataset.drop_duplicates()

# Paste or type your script code here:
import matplotlib.pyplot as plt, datetime
import matplotlib.dates as mdates

dataset['Span Date']=pandas.to_datetime(dataset['Span Date'])
dataset['M Actual Value']=pandas.to_datetime(dataset['M Actual Value'])

pivot=pandas.pivot_table(dataset, aggfunc='first', index="M Actual Value", values='Span Date', columns='M')

for column in pivot:
    clean=pivot[['45 Line',column]]
    clean=clean[clean[column].notnull()]
    plt.plot_date(clean['45 Line'],clean[column],xdate=True,ydate=True,ls='-', lw=1)    
plt.xticks(rotation=0)

dtFmt = mdates.DateFormatter('%Y-%m') # define the formatting
plt.gca().xaxis.set_major_formatter(dtFmt)
plt.gca().yaxis.set_major_formatter(dtFmt)
plt.show()

The final Output is:

Is there any way to display correct days? I notice years (1600+ etc) that are not in my data.

[v-chenwuz-msft · Accepted solution]

Hi @kala2 

It seems that the problem is in your python statement, under the for column in pivot.

Because some rows with NaT, so you should try to remove the null rows.

I'm not particularly skilled in python either. Maybe this code replace it.

plt.plot_date(clean['45 Line'].dropna(axis=0).index,clean[column].dropna(axis=0).values,xdate=True,ydate=True,ls='-', lw=1)

Best Regards

Community Support Team _ chenwu zhu

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