3 Replies Latest reply on Jul 16, 2018 11:15 AM by Saharat Sakulsaowapakkul

# Create calculated measure which is the average sales of previous 12 months

Hi All

I have Sales data from Jan'17 to June 18

I want to create new measure call "Target Sales" which is the sum of previous 12 months / 12 (Average)

When I drag Date dimension month to roll shelf and date dimension year to column shelf together with Target Sales measurement to text mark, I want it to show following.

Year           2018

Month                 Target Sales

January               XXX,XXX  (Sum sales of January 2017, February 2017, .... December 2017) / 12

February             XXX,XXX  (Sum sales of February 2017, March 2017, .... January 2018) / 12

March                 XXX,XXX  (Sum sales of March 2017, April 2017, .... February 2018) / 12

April                    XXX,XXX  (Sum sales of April 2017, May 2017, .... Mar 2018) / 12

May                    XXX,XXX  (Sum sales of May 2017, June 2017, .... April 2018) / 12

June                    XXX,XXX  (Sum sales of June 2017, July 2017, .... May 2018) / 12

Anyone please kindly make the XXX,XXX appear with the logic in the bracket.

• ###### 1. Re: Create calculated measure which is the average sales of previous 12 months

Hi, Saharat

Can you provide a sample workbook?

ZZ

• ###### 2. Re: Create calculated measure which is the average sales of previous 12 months

Table calculations can be used for this, with the usual caveat of taking care with what's on the view. Superstore workbook attached with a calc: window_sum(sum([Sales]),-12,-1)/12

With month on the view, and table calc set to Table Down, this creates that rolling 12 month window.

Note: this assumes you have data for every month, and of course the first 12 months of data will not have useful targets.

1 of 1 people found this helpful
• ###### 3. Re: Create calculated measure which is the average sales of previous 12 months

This is exactly what I am looking for. Thank you for your kindness.