# HOw to calculate 12 Months rolling Average

Hi,

I am working on a webi which should have a calculated column as Rolling Average for last 12 months.

For one day I get 24 records. So for those months which has 30 days I get 720 and with 31 days i getÂ 744.

I researched and referred following threads but nothing worked. Values are still not correct.

how to calculate 16 days moving average in WEBI

How to Create a Moving Average in Webi Report

BOBJ Tricks: Moving Average in Webi

Anyone please help.

Regards

##### Former Member replied

Hi,

You might need a couple of steps to get this done.

**Step 1: **Count the number of days for each month

=Count([Date]) ForAll([Date]) ForEach([Month])

**Step 2: **Calculate total Test value for each month

=Sum([Test Value]) ForAll([Date]) ForEach([Month])

**Step 3: **Calculate running count for month (each month has 1 value, i.e Jan = 1, Feb = 2 and so on)

=RunningCount([Date]) ForAll ([Date]) ForEach ([Month])

**Step 4: **Calculate the total days of the last 12 months

Count =Count([Date]) Where (RunningCount([Date]) ForAll ([Date]) ForEach ([Month]) >=(Max(RunningCount([Date]) ForAll ([Date]) ForEach ([Month])) In Block)-12)

**Step 5: **Calculate the total Test Value during the last 12 months

Sum =[Test Value] Where (RunningCount([Date]) ForAll ([Date]) ForEach ([Month])>=(Max(RunningCount([Date]) ForAll ([Date]) ForEach ([Month])) In Block -1))

**Step 6: **Calculate the rolling average

=[Sum]/[Count]

**Note: You might create new variables for each step above, but do not use these new variable in the calculations from step 1 to 5. All the formulas above need to be in the exact form. Otherwise the calculate context in webi will fail to generate the expected results.**

I hope this helps.

Thanks,

Huu Nguyen