Trend calculation

[Ben83]

I hope you can help me to find a solution for my problem. I want to visualize the trend of my companies delivery reliability (OTIF - on time in full). I have a table which contains every single delivery position of the last year. So there are a lot of positions every calendar week which were rated by tolerances for the delivery date and the delivery quantity. The result is that it is currently possible to show and visualize the delivery reliability of every single delivery position and in that context also the delivery reliability of every single calendar week, every single month, every single quarter and the delivery reliability for the whole year.

My problem is that I want to show a trend of the delivery reliability. It would be great when it would be possible to show the average of the delivery reliability for the last 5 calendar weeks in every single week.

Explanation:
In calendar week 5 the average of the delivery reliability for the calendar weeks 1, 2, 3, 4 and 5.
In calendar week 6 the average of the delivery reliability for the calendar weeks 2, 3, 4, 5 and 6.
In calendar week 7 the average of the delivery reliability for the calendar weeks 3, 4, 5, 6 and 7.
....
In calendar week 20 the average of the delivery reliability for the calendar weeks 16, 17, 18, 19 and 20.
....
And so on.

I would appreciate it if someone can help me to find a solution.

[kcantor · Accepted solution]

I did something similar in our data to show a moving averege of quantiy sold over the past three months. I followed the advice on the following blog in order to capture and quantify the appropriate averages.

http://www.powerpivotpro.com/2013/07/moving-averages-sums-etc/

It was very easy to follow along with this particular blog.

[Ben83]

Awesome! That's what I am looking for. Thank you very much for the usefull help!

[Greg_Deckler]

Well, you will need a measure to calculate delivery reliability. Sounds like you already have that. Then, you will probably want a date/time table, something like DateStream from the Azure Data Marketplace (free). Relate your table with your delivery dates to your date table. Then, you should be able to create a line chart, picking "Week of Year" essentially from the date table and your reliability measure and wahla, you should have your trend as the visualization will context filter your measure by the related week of the year.