Stock rotation

[WilliamFlynn]

 I want to divide the current stock by the average sales for the last 3 months (incl. current month) to give the number of months in stock.

I have built a report that shows on year at a time for all 12 months. Selecting the year displays the months of that year.

The problem is that this year selection means that any months in the previous year are filtered out of the calculation so the first 2 months are incorrectly calculated.

How do I resolve this?

Filtered on 2018

                       Nov 17     Dec 17     Jan 18     Fed 18
Sales                 500            400            100              50
Stock                                                    1,300        1,500
Avg sales (should be)                        400           285
Stock turn (should be)                    3.25          5.26
As the previous years are excluded...
Avg sales   (are... )                              250           202
Stock turn (are...)                             5.20          7.41                                    

[WilliamFlynn]

Sorry for the confusion, I got the numbers wrong (below the corrected numbers)...

                       Nov 17     Dec 17     Jan 18     Fed 18
Sales                 500            400            100              50
Stock                                                    1,300        1,500
Avg sales (should be)                        333           183    (Avg for Jan = (Nov, Dec & Jan) / 3) etc.
Stock turn (should be)                    3.90          8.18
As the previous years are excluded...
Avg sales   (are... )                              100             75
Stock turn (are...)                                 13             20

I use a set analysis to ensure that I have data for all of the previous months but then other filters do not affect the result.
I would like a result that can then react for any subsequent filtering....