Month On Month avg of Sales

[priyapanchi]

Dear All,

I want to to create a pivot which shows me month on month the avg of sale

The avg of sales should be as follows:

On timeline.

when in Apr->Should show avg of Feb,Mar,April

When in May->Should show avg of Mar,Apr,May

When in Jun->Should Show avg of Apr,May,Jun

From July to Mar it should show the avg from Apr to the Particular month

i.e ->

When in Jul ->Should Show avg of Apr to Jul

Kindly find the attached screen shot of the data model.

Data- This table has the transaction wise data.

Master_Cal-This is the Master Calendar

Master_Cal-1- In the table ,i have created Month_AsOf field and MonthYearID

Suppose if the ID for Apr is 13--Master_Cal-1 is having data as follows

Month_AsOf,MonthYearID

13,11

13,12

13,13

14,12

14,13

14,14

15,13

15,14

15,15

16,13

16,14

16,15

16,16

I need to create a pivot having timeline on basis of MonthYear.

Kindly help me with the issue.

[Anil_Babu_Samineni]

Looks like you want only three months backword average. If so, go with set analysis lines

Sum({<month={">=$(=max(month)-3)<=$(=max(month))">} sales)

[priyapanchi]

Dear Anil,

Thanks for the reply.

But i want the Month field in dimension in the pivot.

Thanks,

Priya