Here is the answer for my question.

Code

- Function AverageSemester()
- Dim Ws As Worksheet
- Dim Semester1 As Variant, Semester2 As Variant
- Dim mySemester As Variant
- Dim Target As Range, rngLast As Range
- Dim s As String
- Dim r As Integer, i As Integer
- Application.Volatile
- Semester1 = Array("May", "Jun", "Jul", "Aug", "Sep", "Oct")
- Semester2 = Array("Nov", "Dec", "Jan", "Feb", "Mar", "Apr")
- Set Ws = Sheets(1)
- With Ws
- r = .Range("c" & Rows.Count).End(xlUp).Row - 1
- Set rngLast = .Range("b" & r).Offset(, 1)
- s = .Range("b" & r)
- If isSemes(s, Semester1) Then
- mySemester = Semester1
- Else
- mySemester = Semester2
- End If
- For i = r To r - 12 Step -1
- s = .Range("b" & i)
- If isSemes(s, mySemester) Then
- Set Target = .Range("b" & i).Offset(, 1)
- Else
- Exit For
- End If
- Next i
- AverageSemester = WorksheetFunction.Average(.Range(Target, rngLast))
- End With
- End Function

And it is possible to use a excel a formula as well.

Assuming you do not have more than one year of data in the table, given the workbook you downloaded at the other website, for the Amount average for the previous six **existing** months:

=AVERAGE(IFERROR(GETPIVOTDATA("Amount in USD",A17,"Transaction Date",MONTH(EOMONTH(TODAY(),{-5;-4;-3;-2;-1;0}))),""))

If you might have more than 12 months in the Pivot Table, then you need to check for the year also: