Calculating moving average excluding null values

[Jason Elkin]

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.

[Fabian Duerr]

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.