4 Replies Latest reply on May 9, 2018 3:48 AM by Yamshee Fatima

# How to get last 6 months sales average

Hi,

I am trying to develop a report which need to use 6 months sales revenue average , in my data source, i have monthly sales revenue, I guess I need to create a caculated fields to get the past 6 months average revenue.  at first, I use Table Calculation and seems the result are correct, but when I add a filter into the report, the calculated fields value is not what I need.

This is the table calculation, it looks good:

when I add a filter "isSelectedMonth", the report only show the selected month sales revenue, I hope it can show the average revenue of past month from my selected month, but the average revenue equal to the month revenue, seems table calculation only apply to data in the "current screen", not the whole data source it linked with.

can anybody help me on this? I want to get the average 6 month sales revenue from selected month, the past 6 months' sales revenue are not show in screen, but they are in the data source.

Highly appreicate for any help!!

Kevin

• ###### 1. Re: How to get last 6 months sales average

Hi Kevin,

This things need to be done with the indexes and not with filters as you need previous ranges for calculating the moving average by whatever months you required. Please find the attached image below for reference. I have done this on the superstore data. Also refer the twbx 9.3 which is attached.

I hope this helps you.

Thanks and Regards,

Ashish Chaudhari

• ###### 2. Re: How to get last 6 months sales average

Here is supporting excel calc.

-Ashish

• ###### 3. Re: How to get last 6 months sales average

Hi Ashish,

But I didn't understand why the filter combination of "calculation1"(less than selecte month) and "Calc_filter"( use Last() function for table calculation) can get right answer, but my "isSelectedMonth" got wrong data. Why the Last() function can only show specific data and "Hide" other data in RAM?

Anyway, I already know how to do it, but just curious why your solution can work.

Best Regards,

Kevin Meng

• ###### 4. Re: How to get last 6 months sales average

Here you are using Parameter to get the month, but in my situation I need to use the filter as parameter does not update automatically.