I did the following script to take the last three months'/ rows average when it would otherwise be a null or len = 0. However I want to only count the last three rows that have values. This only works if the last three rows have data. If one of the previous months /rows has a zero value it counts that zero I need it to count only the values greater than 0.
=if(IsCurrentMTD = '1', sum(WITNESS_POINTS_SCORED)/sum(WITNESS_POINTS_POSSIBLE),
If(len(trim(sum(WITNESS_POINTS_SCORED)/sum(WITNESS_POINTS_POSSIBLE)))= 0 ,
aggr(RangeSum(Above(sum({<[Month-Year] = 1::[Month-Year] >} WITNESS_POINTS_SCORED), 1,3)), Agent, [Month-Year]) /
aggr(RangeSum(Above(sum({<[Month-Year] = 1::[Month-Year] >} WITNESS_POINTS_POSSIBLE), 1, 3)), Agent, [Month-Year]) ,
sum(WITNESS_POINTS_SCORED)/sum(WITNESS_POINTS_POSSIBLE)))
