Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try something like this
sum( aggr( rangesum( above( total sum( {<[FY]=, Month=, [Report Month]=>} Sales),0,3)),[Report Month]))/3
Thanks for reply, but trying to understand how this expression works? Can you please explain it, would be great help.
-vsap2000
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.