Dear All,
I have data contains 2 Fiscal year (Current FY and Previous FY)
- Previous FY = Oct 2018 - Sept 2019
- Current FY = Oct 2019 - Sept 2020
I have requirement to show moving average of 3 months in line chart
The issue is the user wants a filter that allows them to select value Previous FY/Current FY
And When they select Current FY, it will only show Oct 2019 - Sept 2020 Data. But for Oct 2019 data it will be calculated from AVG(Aug 2019+Sept 2019+Oct 2019).
I tried to create parameter for user to select Previous FY and Current FY instead of using filter
But looks like the window_sum doesn't work if the month aug, sept is not in the view
Attached is the workbook
Thank You
hi
your issue is that moving average is a table calculation - and they operate on the table that underlays the worksheet where they are applied - when you filter a worksheet you remove data from that table so the values are no longer there to determine a moving average - the approach is use a show / hide filter - does not filter the table but creates a viewing window of data in the table see https://jimdehner2.blogspot.com/search/label/Show%20Hide%20Filter
it will show you different ways to create the window
Jim
If this posts assists in resolving the question, please mark it as “Best Answer” or Upvote. Thank you.
