Forum Discussion
Rajendran_J
Oct 21, 2023Copper Contributor
Excel Pivot table must filter based on the date value in a cell
Dear Team,
I want the Pivot table to automatically filter Settled Date based on the Target Date.
I expect Settled Date to be greater than or equal to the Target date value in J2.
Expected result:
Thank you,
Rajendran
Attached you will find editable sample file link:
Filter Date with Specific value in Cell.xlsx
As variant creating PivotTable add data to data model plus add target data to data model as another table.
Create DAX measure
Amt:=VAR target=VALUES(Table1[Target Date] ) RETURN CALCULATE( SUMX( Range, IF(Range[Settled Date] >= target, Range[Amt ₹], BLANK() ) ))
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("J2"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False With Me.PivotTables("PivotTable1").PivotFields("Settled Date") .ClearAllFilters If IsDate(Range("J2").Value) Then .PivotFilters.Add2 Type:=xlAfterOrEqualTo, _ Value1:=Format(Range("J2").Value, "Short Date") End If End With Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open the workbook.