Filter Google Sheets pivot table for current month

0
votes

I have a Google Sheets pivot table of transactions with dates. The source data is updated daily. I know how to manually set a filter for all transactions in a month, for example, dates between 02/01/19 and 02/29/19.

What if I want to only see all data in the current month without manually selecting that specific month? If it's March 15, I only want to see transactions from March 1 to the current date and have it updated automatically when April comes without having to manually select April.

Do you have any ideas on how to do this?

3
dategoogle-sheetspivot-table

3 Answers

1
votes

I think it's easy with Google Sheets! Use FILTER function somewhere on another sheet with link to your table by just selecting columns with data:

Example: =FILTER(Sheet_with_data!A:B;month(A:A)=month(today()),

where A:B is range with your data, A:A is column with dates

But make sure that date format is correct. The filters values will update automatically.

Good Luck

1
votes

You can use a slicer with an option of "Filter by condition" and set the condition to "is between" the first day of the current month and the last day of the current month.

The first day can be input as a formula like

=date(year(today()), month(today()), 1)

while the last day is clearly

=eomonth(today(),0)

enter image description here

0
votes

directly in the pivot table, it's not possible so you need to use some formula to trim it and then use pivot table from there:

=QUERY(A:B, "where month(A)+1 matches '"&MONTH(TODAY())&"'
               and A <= date '"&TEXT(TODAY(), "yyyy-MM-dd")&"'")

0