Find the last semester using Function LastQuarter + 2

Question

I need a code that will count how many months was active in the past semester.

I have a pivot and the data changes all the time when the pivot is refreshed, However, I need to calculate the last 6 months average but only using the months that was active in the last semester. e.g in this case I would take the average for the last 6 months will only use 4 months (2020 Jan, Feb, Mar & 2019 Dec) because it is the months there person was active in the last semester).

The rule is the semester start in the month we are now (April) and it goes back 6 months. Apr2020, Mar2020,Feb2020,Jan 2020 & Dec2019,Nov2019 and from this months we would take the amount only for the 4 months the client as active like in the image

would it be possible to use

Public Function LastQuarter(theDate As Date) As Date + 2 months to get the last semester? 
DateSerial(Year(theDate), 2)+2

However, I am guessing I need to use IF the last 6 months is = 6 active months in a row then go to code that is already done

else

But IF the last 6 months is < 6 active months in a row Then count the number of active months in the last semester (using the Function LastQuarter(theDate As Date) As Date + 2 months) and with the total amount of this active months and do the average of the values. cells(number of active months, 3).value ="Average"

ps: I posted a similar question on https://www.ozgrid.com/forum/index.php?thread/1227330-how-to-calculate-how-many-months-is-active-in-a-semester/#wcf9?

Answer 1

Try,

This is macro an UDF.

d5 = AverageSemester()

Below is macro.

Sub Test()
    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

    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
        .Range("d5") = WorksheetFunction.Average(.Range(Target, rngLast))
    End With
End Sub
Function isSemes(s As String, vSemester As Variant)
    Dim v As Variant
    For Each v In vSemester
        If v = s Then
            isSemes = True
            Exit Function
        End If
    Next
End Function
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

When September is the last data

Average range ~~> May, Jun, Jul, Aug , Sep

When January is the last data

Average range ~~> Nov,Jan

Answer 2

You can use the GETPIVOTDATA function.

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}))),""))

For a non-existent month, the formula returns an error. The IFERROR function converts that to a null string, which will be ignored by the AVERAGE function.

You can make the obvious changes for Averaging the Bene Frequency column, and extending the array constant to include 12 months instead of 6

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

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

Answer 3

Did you look into EDATE function?

=IF([Date you want to check]<EDATE(now(),-6),TRUE,FALSE)