Jump to content

Calculating moving average excluding null values

Jason Elkin

Recommended Posts

I need to cacluate a rolling average of the usage rate field in a calculated column. The rolling average would include the 3 most recent values that are not null or 0 relative to the current row. Usage rates are null when the volume increases relative to the prior date.I haven't been able to use navigation methods that would normally work with dates since the dates corresponding to the values will generally be non-consecutive or the time period between dates will vary. I've attempted various versions of the following with no success.

Avg([usage Rate]) Over (Intersect([Name 1], [Name 2], PreviousPeriod([usage Rate],3)))

Since I have inconsistent dates for my usage values these typical functions for navigating don't seem to work. I was able to get a version of this to work on a bar graph where each bar is considered a node; however, I can find a solution for a calculated column.

How would I create a calculated column with a rolling average for a current row of data that is limited to the last 3 available values for that field I've attached an excel sheet that has the dates and usage values available. Thank you.

Link to comment
Share on other sites

Please check the help for the LastPeriods() function.

Unfortunately the columns in your Excel sheet do not match the formular in the question. But please try something like:

If([usage Rate] is not null, Sum([usage Rate]) OVER (LastPeriods(3,[Date.Column]))) / 3The IF statement ensures that empty rows for [usage Rate] are skipped.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...