Average over sets of consecutive periods of time.
I want to creat a calculated column which calculates an average over a time frame. The time frame gets larger as the data set progresses. i.e. I want to include the previous time point in the next average.
So the average for 'TP3' should be the average of all the data, not just data at 'TP3'. The average for TP2 should include all data from TP1 and TP2.
I've tried the following:
when [Time Point]='TP1' then If([Time / Days]<=3,Avg([Storage Temperature / °C]),NULL)
when [Time Point]='TP2' then If([Time / Days]<=6,Avg([Storage Temperature / °C]),NULL)
when [Time Point]='TP3' then If([Time / Days]<=9,Avg([Storage Temperature / °C]),NULL)
However the "when [Time Point]='TPX'" seems to be limiting the data the following if statement sees, so the returned average is only pertaining to TPX (i.e. the average for TP3 returns as 25.8)
Does anyone have suggestions for a fix?