As a workaround, you could just add the last 3 periods together in a running total and then divide by 3.
Something like:
Dim Period1 As Decimal = api.data.GetDataCell("Cb#XXXXXX:T#Pov:A#Direct_HC:S#Actual:E#XXXXX:F#EndBal_Input:V#Periodic:O#BeforeAdj:I#None:U3#Top:U4#Top:U5#Total_Adj_Alloc:U2#" & U2 & ":U7#Top:U8#None").CellAmount
Dim Period2 As Decimal = api.data.GetDataCell("Cb#XXXXXX:T#PovPrior1:A#Direct_HC:S#Actual:E#XXXXX:F#EndBal_Input:V#Periodic:O#BeforeAdj:I#None:U3#Top:U4#Top:U5#Total_Adj_Alloc:U2#" & U2 & ":U7#Top:U8#None").CellAmount
Dim Period3 As Decimal = api.data.GetDataCell("Cb#XXXXXX:T#PovPrior2:A#Direct_HC:S#Actual:E#XXXXX:F#EndBal_Input:V#Periodic:O#BeforeAdj:I#None:U3#Top:U4#Top:U5#Total_Adj_Alloc:U2#" & U2 & ":U7#Top:U8#None").CellAmount
Dim Avg As Decimal = (Period1 + Period2 + Period3)/3
I ended up doing something very similar! I added logic to manually calculate the average for only the time periods that cross years.
If Not monthNumber = "2" And Not monthNumber = "3" Then
Dim startingdb As String = "Cb#XXXXXX:T#" & TimePrior & ":A#" &
Denominator & ":S#Actual:E#XXXXXX:F#EndBal_Input:V#Trailing3MonthAvg"
db_Denominator = api.Data.GetDataBufferUsingFormula(
"RemoveZeros(FilterMembers(" & startingdb & ", " & filter1 & ", " & filter2 _
& "))",, True)
Else
Dim startingBuffer2m1 As String = "Cb#XXXXXXX:T#" & TimePrior & ":A#" _
& Denominator & ":S#Actual:E#XXXXXXXX:F#EndBal_Input:V#Periodic"
Dim startingBuffer2m2 As String = "Cb#XXXXXXX:T#" & TimePrior2 & ":A#" _
& Denominator & ":S#Actual:E#XXXXXXXX:F#EndBal_Input:V#Periodic:"
Dim startingBuffer2m3 As String = "Cb#XXXXXX:T#" & TimePrior3 & ":A#" _
& Denominator & ":S#Actual:E#XXXXXX:F#EndBal_Input:V#Periodic:"
Dim Denominator1 As DataBuffer = api.Data.GetDataBufferUsingFormula(
"RemoveZeros(FilterMembers(" & startingBuffer2m1 & ", " & filter1 & ", " & filter2 _
& "))",, True)
Dim Denominator2 As DataBuffer = api.Data.GetDataBufferUsingFormula(
"RemoveZeros(FilterMembers(" & startingBuffer2m2 & ", " & filter1 & ", " & filter2 _
& "))",, True)
Dim Denominator3 As DataBuffer = api.Data.GetDataBufferUsingFormula(
"RemoveZeros(FilterMembers(" & startingBuffer2m3 & ", " & filter1 & ", " & filter2 _
& "))",, True)
api.Data.FormulaVariables.SetDataBufferVariable("Denominator1", Denominator1, False)
api.Data.FormulaVariables.SetDataBufferVariable("Denominator2", Denominator2, False)
api.Data.FormulaVariables.SetDataBufferVariable("Denominator3", Denominator3, False)
Dim db_DenominatorAvg As New DataBuffer
db_DenominatorAvg = api.Data.GetDataBufferUsingFormula(
"($Denominator1+$Denominator2+$Denominator3)/3")
'db_DenominatorAvg.LogDataBuffer(api, "db_DenominatorAvg" & Denominator, 1000)
db_Denominator = db_DenominatorAvg
End If