Rolling 3 Month with Aggr on two dimensions

[Jahnavi1191]

Hello,

I need to show Rolling 3 Months (Offset of 1 month i.e in Sept show: Jun+Jul+ Aug). The calculated is to be made at Parent ID level and by month but displayed by Child Id, Parent ID and Month like screenshot attcached. I was able to go through a lot of posts and this formula works when I have 1 or more selections but by default it shows NULL . Kindly help me with the right formula .

Formula I am using:  Aggr( RangeSum(Above(Sum([Job Count]), 1, 3)), [Year Month])

#aggr #pivot #Set

@sunny_talwar Could really use your help here. 

[Kushal_Chawda]

@Jahnavi1191  could you please elaborate? What is the expected output you need? If you could create excel file with expected output will be helpful

[Jahnavi1191]

@Kushal_Chawda : Thank you for checking this, The output excel is attached to the post. Here is the excel version, for April I want to show jobs by Jan + Feb + March calaculate at Parent ID level.

[Kushal_Chawda · Accepted solution]

@Jahnavi1191  try below

=sum(aggr(rangesum(above(sum([Jobs by Month]),1,3)),[Parent ID], (YearMonth,(NUMERIC,ASCENDING))))

[kishore3k · Accepted solution]

@jahnavi11 

in other way, Create a as-of Calendar and link every month in master to previous three months in as-of calendar like below.

Master Calender          As-of Calendar

Jan21                             Apr21

Feb21                             Apr21

Mar21                             Apr21

Feb21                             May21

Mar21                             May21

Apr21                             May21

If you use the As-of calendar - Year month column for the same expression, it will calculate for  rolling previous three months and absolute numbers for Master Calendar numbers.

please below screenshot except you have to map previous three months instead  of equivalent+previous 2 months.

Hope this helps.