Rolling 3 Months for bar chart

[vsap2000]

Hi,

I have button for fiscal year 2019 and 2020 (Fiscal yr starts from Apr month) &  I am creating bar chart for Rolling 3 months of Sales from Current Month Thru Past 13 Month.

For eg if ,

Feb 19 = 100, Mar 19 = 100 ,Apr 19 = 100, May 19 = 100, Jun 19 = 100, Jul 19 = 100, Aug 19 = 100, Sep 19 =100

so for Rolling 3 month my bar chart shows for Sep 19 = (Sep 19 + Aug 19 + Jul 19)/3  i.e (100+100+100/3) = 100,

Aug 19 = (Aug 19+Jul 19+Jun 19)/3 = 100  it works well for all 13 month when my button is not selected,

when I select Fy = 2020, for Jun 19 thru Sep 19 calculates correct but for May 19 it will show (Apr 19+May 19) = 200/3 and for Apr 19 will show 100/3,

my question is how I can calculate for May 19=  (May 19 + Apr 19 + Mar 19)/3= 300/3  &

for Apr 19 = (Apr 19 + Mar 19 + Feb 19)/3 = 300/3 when button FY =2020 is selected.

My Bar is Report Month and Measure is Sum(Sales)/3 for each month.

Any help is appreciated.

Thanks in advance

-vsap2000

[Vegar]

Try something like this

sum( aggr( rangesum( above( total sum( {<[FY]=, Month=, [Report Month]=>} Sales),0,3)),[Report Month]))/3

[vsap2000]

Thanks for reply, but trying to understand how this expression works? Can you please explain it, would be great help.

-vsap2000

[Vegar]

I would recommend you to take a look at @Gysbert_Wassenaar  post Calculating rolling n-period totals, averages or other aggregations.

It should help you getting to understand the logic behind the expression I sent you.