Greetings,
I have a dataset which is just two columns: a date, and a value for that date. For example:
7/1/2016 1,542,151
6/24/2016 4,564,222
6/17/2016 9,654,123
What I am trying to do is find the minumum value for the 5 prior years for the same week number of each datapoint.
For example, on 7/1/2016, this was the 27th week of the year. I am wanting to see the minimum value for the 27th week of 2011-2015. My goal will be two lines displayed on the chart - the current value, and the smallest value in the Nth week of the last 5 years (excluding this year).
Any idea how to do this?
Thanks!
I am sure there is a more elegant way of doing this...but here is one way.
I created a random data set.
Next, I created a calculated field "Min by week" using the following
{FIXED [Week] : min([Value])}
Then I created a chart to show an average value by week and the minimum value for that week regardless of the year. To simplify, I selected Year(date) as a filter.
Does this help?
