Remove the highs and low weeks of a rolling average across N weeks

[cworkman2015]

Good afternoon everyone,

I'm trying to identify high and low sales weeks in a summarized group of data in a rolling 6 weeks.

Thus far I'm able to build the filtered 6 week period and summarize them as sales units by week.

var filtered_table = FILTER(CONFORM_MOVEMENT, CONFORM_MOVEMENT[is_summary] <> 1 && CONFORM_MOVEMENT[date_key] > max(CONFORM_MOVEMENT[date_key]) - 42)

var summarize_table = SUMMARIZE(filtered_table, 'Calendar'[Week],"sales_units_by_week", sum(CONFORM_MOVEMENT[SalesUnits]))


return maxx(topn(1,summarize_table,[sales_units_by_week], desc),'Calendar'[Week])

When I attempt to return the maxx week of the rolling 6 weeks it brings over the current week rather than the top in the last 6 weeks.

My expectation would be this on May 9th record looking back 6 weeks i would identify the high week to be April 11th and the low week to be april 4th. However, on Mar 28th i would see the high week as march 21st and the low week as february 21st. 

Any suggestions would be greatly appreciated!

Thanks

Channing

[Anonymous]

If you don't get answers, it means people don't understand your description of the problem...