Filtering both year and month in Visibleitemslist for an OLAP pivot table using VBA-CodePudding

I have been trying to automate this macro that's filtering the date range depending on the input date field(I have given the input date as July 2022). This date range filter should be applied in such a way that it should include three years before the input year(i.e. 2020-2022) and all the months before the input month(jan-july).

I'm not sure how to create a generalized for loop for the filter below that will automatically change the year and month according to the input field. I have tried to create a for loop for just the month for the current year but don't know how to combine the ranges for the previous three years as well.

Sub Macro2()
'
' Macro2 Macro
'

'
ActiveSheet.PivotTables("NetRate22").PivotFields( _
"[Policy State Dimensions].[Effective Date].[Effective Month]"). _
VisibleItemsList = Array( _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2020]&[1]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2020]&[2]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2020]&[3]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2020]&[4]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2020]&[5]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2020]&[6]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2020]&[7]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2021]&[1]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2021]&[2]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2021]&[3]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2021]&[4]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2021]&[5]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2021]&[6]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2021]&[7]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2022]&[1]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2022]&[2]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2022]&[3]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2022]&[4]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2022]&[5]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2022]&[6]", _
"[Policy State Dimensions].[Effective Date].[Effective Month].&[2022]&[7]")

End Sub

I have been trying to solve this for a month now and haven't found the best resources in VBA to help with this solution. Any help with this problem is appreciated. Thanks!

CodePudding user response：

Untested.

Sub Macro2()

   ' visible item list
    Const VIL = "[Policy State Dimensions].[Effective Date].[Effective Month]"
    Const YRS = 3 ' prior years

    Dim dt As Date
    dt = "1/7/2022" ' July 2022
     
    Dim mth As Long, yr As Long
    Dim m As Long, y As Long, i As Long
    Dim ar
   
    mth = Month(dt)
    yr = Year(dt)
    ReDim ar(0 To mth * YRS - 1)

    ' build array
    i = 0
    For y = YRS To 1 Step -1
        For m = 1 To mth
           ar(i) = VIL & ".&[" & (yr - y   1) & "]&[" & m & "]"
           i = i   1
        Next
    Next
    'Debug.Print Join(ar, vbLf)
    
    ActiveSheet.PivotTables("NetRate22").PivotFields(VIL).VisibleItemsList = ar
    
End Sub