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

Milestone Trend Analysis Using Python

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:

kala2_0-1634578573051.png


My dataset is in the following format: 

kala2_1-1634579081367.png


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:

kala2_2-1634579162575.png

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




1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

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)

vchenwuzmsft_0-1634797007477.png

 

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.

View solution in original post

1 REPLY 1
v-chenwuz-msft
Community Support
Community Support

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)

vchenwuzmsft_0-1634797007477.png

 

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.

View solution in original post

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.