Help with monthly rolling AVG

[Anonymous]

Hi All,

I have data with list of item nos for which i want to show avg for last 2/3 month.

I have attached test qvw which consist data for only one item no.

Tried formula: =avg(Consumed) & =aggr(Avg(Consumed),MONTH,[Item No])

however its not showing proper avg..

Would like to show monthly avg for all item no in pivot table & for some items in text box like shown in above image. 

[prat1507 · Accepted solution]

Use

=(FirstSortedValue(Aggr(Sum(Consumed), MONTH,[Item No]), Aggr(Sum(DISTINCT(MONTH)), MONTH,[Item No]), 1)

+FirstSortedValue(Aggr(Sum(Consumed), MONTH,[Item No]), Aggr(Sum(DISTINCT(MONTH)), MONTH,[Item No]), 2))/2

[ajaysingh29]

Try this:

=AVG(Aggr(sum(Consumed),MONTH,[Item No])) 

[Anonymous]

Thank you