Select the source data, and click Insert > PivotTable. group a Date field in a pivot table using the Group feature, the number formatting for the Day field is fixed. Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by Quickly transform your data (without VLOOKUP) into awesome reports! If this a report to be repeated with different data in your table, you need to make sure that new values will also be taken into account: I want to filter the date as necessary in the pivot table, but the filter only shows me each and every day...not grouped data by year which can be drilled down as desired. You can filter by a particular date range, for example: by this week, next month, next quarter, next year, last year, year to date and the list goes on and on. I have a pivot table where I am trying to update the Date filter based upon a date range input by the user. With the data in our Pivot table, we can use the value filter to check all client expenditure for those lesser than a particular amount. Multiply no of days in the date range by expense per day 3. I would rather not use VBA but not apposed to it. Please do as follows: 1. I have a data set that I want to use in a Pivot table. This option is for normal Auto Filter, not Pivot Table, so don’t get confused. How to create a pivot table in Microsoft Excel. I have created columns in my data table for Day, Month and Year from the Sales Date, and use slicers for those to drill down the data each month. Here is the documentation for DATESINPERIOD() function. Work out how many days of this expense fall within the date range 2. I would like to filter a date field in a pivot table by a date range using the filter function. 1. These filter buttons enable you to filter all but certain entries in any of these fields. This is how the group dates in Pivot Table by week will be displayed. 12. ... Let users filter the PivotTable by date components. Filters in Pivot tables are not similar like filters in the tables or data we use, in pivot table filters we have two methods to use filters, one is by right click on the pivot table and we will find the filter option for the pivot table filter, another method is by using the filter options provided in the pivot table fields. Ideally, you’d be able to refresh the pivot table and have it update without having to remember to specify the filter again for each day or month. Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. For example, we can easily filter for all sales before Jan 1, 2013. We can use these items to manually filter sales data. Click the drop down arrow on the Row Labels heading Select the Field name from the drop down list of Row Labels fields Refreshing the pivot table will not pull in the extra days data as the data range is still fixed. Now in the Date Filter (Date) dialog box, specify the certain date range, and click the OK button. 10. Work out how many days of this expense fall within the date range 2. For example, if you have retail sales data, you can analyze data for each region by selecting one or more than regions (yes, it allows multiple selections as well). The Date filter sub-menu provides a long set of presets for dated information. There are an array of different Date filters in a Pivot Table. If it still doesn't solve the problem, you can share your data and that way, I can help you better. Group Dates in the Date Field. Suppose we have a data source which are dates from 2019 to 2021. I have tried several different variations and have not gotten it to work. Please enter the email address for your account. Run the report; Open the report. Go to the pivot table, please click the arrow beside Row Labels, uncheck the dates you will hide, and click the OK button. If you like this Excel tip, please share it. I have a pivot table where I am trying to update the Date filter based upon a date range input by the user. Referencing an Excel Pivot Table Range using VBA. In a normal table or range, it’s easy to filter data by clicking Data > Filter, but do you know how to filter in a pivot table? For example, we can easily filter for all sales before Jan 1, 2013. Group by dates. Emily Parsons. Example, I was using 31-Mar-20. Let’s look at these filters one by one: Report Filter: This filter allows you to drill down into a subset of the overall dataset. 3. NOTE: Pivot Tables are one of the INTERMEDIATE EXCEL SKILLS. Date Total Revenue 10-Aug $83,004 In the above example, we had learned of creating a filter in Pivot. What’s the quickest way to filter that PivotTable based on an external list that contains either 100, 10000, or 19900 of those items? The 4 Step Framework to ADVANCE Your Excel Level within 30 DAYS! To filter by date range on an individual pivot table do the following: Click on pivot table to bring up Pivot table editor Scroll down to Filters Click Add and select Date field (1) If you need to filter out the specified date range in the pivot table, please click the arrow beside Row Labels, and then click Date Filters > Before / After / Between in the drop-down list as you need. For example, you can also use a pivot table to group data by numbers – summarizing data by age range, price range, or any numerical range that makes sense for your data. When I select that column (date format) in either Filter or column or row, pivot table doesn't look like it recognizes that column in date format, and treat it like a text. For example, if you have retail sales data, you can analyze data for each region by selecting one or more than regions (yes, it allows multiple selections as well). When date data is treated as a date, pivot table shows it as hierarchy format, year, then month, then day, which can be expanded to show what's below and contracted to only show year or month. Example, I was using 31-Mar-20. I would rather not use VBA but not apposed to it. Since that version, dates in Pivot tables have been automatically grouped too. Multiply no of days in the date range by expense per day 3. This is useful if you want to see what invoices are due to be paid this month or what sales transactions were included in a particular quarter. =IF(WEEKDAY(TODAY())=2, TODAY()-3, TODAY()-1) how do I get the pivot table to use the cell value as the date filter. Otherwise, you could add a field to your data table with a comparison like =A2>DATEVALUE("4/1/2015") And use that TRUE/FALSE as the page filter. Skill level: Intermediate Pip has a set of pivot table based reports that she updates frequently (daily, weekly, monthly). She wants to automatically filter the reports for the most recent date in a column in the data set. Filter a Pivot Table for a Date Range In a pivot table, you might want to see all the orders that were shipped on a specific date. Go to the pivot table, you will see the Date field is added as report filter above the pivot table. Your email address will not be published. To group the dates by week, follow the steps below: STEP 1: Right-click on one of the dates and select Group. Select “Month” in the group by option and then click OK. Quick Tip: You can also use above steps to group dates in a pivot table by years, quarters, and days. 4. Select any of the cells from the date column. Multiply no of days in the date range by expense per day 3. If we want to know the client(s) that spent less than 10,000 at once, we will do the following: We will click on the drop-down arrow of Row Labels; We will click on value filters In a pivot table, you can use date filters to show the results for a specific date or date range. 2. I have created a cell formula that will give me the date I want to filter on. There are three types of date filters: Individual date checkboxes ; Selection by specific date range; Dynamic date range selection ; Filter With Date Checkboxes Required fields are marked *. Is there another way to accomplish this? Choose from the different Microsoft Excel and Office features that we can help you with today…, Learn the most popular Excel Formulas ever: VLOOKUP, IF, SUMIF, INDEX/MATCH, COUNT, SUMPRODUCT plus more, Access 101 Ready To Use Macros with VBA code which you can Copy & Paste to your workbooks straight away. Please click the arrow beside (All), check Select Multiple Items option in the drop-down list, next check dates you will filter out, and finally click the OK button. 3 most important ways of displaying a list of multiple items in a pivot table filter are:-Using Slicers For example, in the table shown below, you could show just the orders that were shipped this week. something be done to the properties of the pivot table filters so that it will examine each start and end date and 1. Your date format in cell A5 and in pivot filter should be same. Open and create multiple documents in new tabs of the same window, rather than in new windows. All rights reserved. I have created a pivot table from a data set. DOWNLOAD OUR FREE EXCEL RESOURCE GUIDE E-BOOK! This website uses cookies to ensure you get the best experience on our website. Or you can set a start time and time interval to define your date range. 1. Pivot tables has the one of the most useful features to group the items which is can be used on items of row label or column label. Now let’s look at the way we display the list in different ways. STEP 1: Go to Row Labels and select Date Filters > Between. Here is the way that I do it right now. In the PivotTable Fields pane, please drag and drop the Date field as the first-row label, then drag and drop other fields to different sections as you need. Let’s look at these filters one by one: Report Filter: This filter allows you to drill down into a subset of the overall dataset. http://www.contextures.com/tiptech.html#PivotTables Add pivot table date filters to focus on specific dates or range of dates. Many people stop with this basic automatic grouping, but pivot tables can also group data in more sophisticated ways. 1. 1) Pass your filter table and your data table to the Data Model (Power Pivot): Connect them on the filter columns. When I toggle the 'Sort Oldest to Newest' in the pivot table, my dates seem to be sorted alphabetically. We can insert a pivot table timeline for filtering our pivot table dates. http://www.contextures.com/tiptech.html#PivotTables Add pivot table date filters to focus on specific dates or range of dates. 2. To create a pivot table that shows the last 7 days of data (or, more generally, the last n days) you can add a helper column to the source data with a formula to flag records in the last 7 days, then use the helper column to filter the data in the pivot table. Higher in the menu, the entry for Label filters has been replaced by Date filters. © Copyright 2020 MyExcelOnline SLU. 3. Bottom line: Learn how to filter a pivot table, pivot chart, or set a slicer for the most recent date or period in the data set. Get our Free Weekly Excel lesson plans that cover the must know Excel features and tips to make you better at Excel! How to automatically refresh a Pivot Table in Excel? I am trying to run pivot table reports to understand where we can optimize projects. If it still doesn't solve the problem, you can share your data and that way, I can help you better. [vb]Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub Dim ptPvt As PivotTable Dim ptFld As PivotField Dim i As Long Dim Flg As Boolean Dim dtBegin As Date Dim dtEnd As Date This is especially likely to occur if you used a large date range or used Day granularity. A timeline is just like a slicer, the difference is that timeline only filters dates.. The to and from dates are stored in cells so the user may modify them. Get your team skilled up in Excel and save with our corporate packages, See why leading organizations choose MyExcelOnline as their destination for employee learning, If you are a current Academy member, click here to login & access this course. August 25, 2017 - 2:23 am. One of the fields in the data set is a date field (and I've checked, Excel is recognising it as a date in the format mm/dd/yyyy). Drop fields into the Report Filter area of the pivot table layout. See screenshot: 4. Jeff here again. The Date filter sub-menu provides a long set of presets for dated information. Table 2 = VAR DateStart = DATE ( "2018", "1", "1" ) RETURN CALCULATETABLE ( TEST, DATESINPERIOD ( TEST[Date], DateStart, 15, DAY ) ) You can define the time interval by day, month, season, year. In the Create PivotTable dialog box, please select a destination range to place the pivot table, and click the OK button. The macro below … Types of Filters in a Pivot Table. However, when I add this field to the rows of my pivot table, it only displays the month in text format (ie, "Feb"), and then creates a separate column with the year. (2) If you want to filter out a dynamic date range in the pivot table, please click the arrow beside Row Labels, and then in the drop-down list click Date Filters > This Week, This month, Next Quarter, etc. How to sort by sum in Pivot Table in Excel? You may get messages about the table being too wide. But with Kutools for Excel’s Combine (worksheets and workbooks) utility, you can get it done with just several clicks! So today in this post, I’d like to share with you simple steps to add a timeline filter to filter dates in a pivot table. It has the following format “Day-Month” or “d-mmm”.If we try to change the number format of the Day/Date field it does not work See screenshot: 3. Group Dates in Pivot Table by Week. Say that your data set had four years’ worth of sales, and you filtered to This Year. Your email address will not be published. I have a pivot table which lists a count of how many inventory items were sold between a date range. Multiply no of days in the date range by expense per day 3. In the example shown, the current date is August 16, 2019, and the pivot table shows the seven days previous. Now I need to count the number of students by score range. When drag and drop the date field as the first-row label, you can filter date range in the pivot table easily. See screenshot: Now you have filtered the date range in the pivot table. We can group items in a following way. 5. See screenshot: Now you have filtered out the specified date range in the pivot table. In this example, the Order Date field is in the Report Filter area, and you can select a specific date, to see its orders. There are an array of different Date filters in a Pivot Table. 1. In the source data for your pivot table, you can use the drop down filters in the heading cells, to show only the records for a specific date range. We can use a simple macro to set the filter in the pivot table for the latest date in the source data table. Excel Pivot Table Properties & Settings, using VBA. 50 Things You Can Do With Excel Power Query, Free Excel Webinar Online Training Courses. Group by dates; Group by numbers; 1. Excel Pivot Table Address, Location, Move & Copy using VBA. I have created a cell formula that will give me the date I want to filter on. STEP 3: Let us try another one. Hi. 026: The Best Microsoft Excel Tips & Tricks in 2019! Hello, New to using VBA and thankful that I came across this forum. This process is different from filtering. Now in the Date Filter (Date) dialog box, specify the certain date range, and click the OK button. Select the table, and click Insert > PivotTable. Your pivot table is now filtered by the dates! In the Create PivotTable dialog box, specify the destination range to place the pivot table, and click the OK button. Supposing I have a table of student scores as below screenshot shown. Here’s a pivot table in which I added … 80%, Convert Between Cells Content and Comments, Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier, Filter date range in Pivot Table with adding date field as row label, Filter date range in pivot table with adding data filed as report filter. In these pivot tables I would like to filter by a date range. Re: Filter Pivot Table by Date Range. There are no reviews yet. Types of Filters in a Pivot Table. =IF(WEEKDAY(TODAY())=2, TODAY()-3, TODAY()-1) how do I get the pivot table to use the cell value as the date filter. Ungroup Month in Excel Pivot Table. I have a pivot table that needs to be filtered to show all the records before the current date. If your Sheet name, Pivot table name, and filter name are different from what is there in code, you need to make those changes on your own. Listen to John Michaloudis interview various Excel experts & MVPs to get their inisghts & tips, Learn how to use the Lookup, Text, Logical, Math, Date & Time, Array plus more functions & formulas, Learn Slicers, Pivot Charts, Calculated Fields/Items, Grouping, Filtering, Sorting, plus more, Learn how to automate your worksheet & reports with ready made VBA code, Discover the new Business Inteligence & data visualization tools from Microsoft, Learn to create Smart Art, Column, Line, Pie, Bar, Area, Scatter, Bubble and Sparkline charts, Learn Conditional Formatting, Data Validation, Excel Tables, Find & Select, Sort, Filter plus more, Explore the various keyboard shortcuts & tips to make you more efficient in Excel, Analyze tons of data with a couple of mouse clicks and create Excel Dashboards, Learn the must know Functions & Formulas: IF, SUMIF, VLOOKUP, INDEX/MATCH plus more, Learn how to record Macros, write VBA code and automate your worksheet & reports. Insert a pivot table. Save my name, email, and website in this browser for the next time I comment. something be done to the properties of the pivot table filters so that it will examine each start and end date and 1. Filters in Pivot tables are not similar like filters in the tables or data we use, in pivot table filters we have two methods to use filters, one is by right click on the pivot table and we will find the filter option for the pivot table filter, another method is by using the filter options provided in the pivot table fields. Value Filters. 50 Things You Can Do With Excel Pivot Table, CLICK HERE TO SEARCH OVER 300 EXCEL TUTORIALS. If, for example, you had a customer who purchased from you in 2017 and 2018 but not in 2019, that customer wouldn’t appear in the pivot table. Aug 24, 2016 #1 Hello, New to using VBA and thankful that I came across this forum. To create the example PivotTable, do the following: Click any cell in the data set. Thread starter FarmGuy; Start date Aug 24, 2016; F. FarmGuy New Member. How to convert dates into months/ quarters/ years in pivot table. 50%, and reduces hundreds of mouse clicks for you every day. Let’s understand how to convert dates into months/ quarters/ years in pivot table with example. No - if you want to use date filters, then the date needs to be a row or column field. We might as well rename this blog Daily Dose of Pivot Tables, because here’s yet another treatment of this well-worn subject this week.. Let’s say you’ve got a PivotField with 20,000 items in it. * Filter date range in Pivot Table with adding date field as row label You can filter by a particular date range, for example: by this week, next month, next quarter, next year, last year, year to date and the list goes on and on. When you create a new pivot table, you’ll notice that Excel 2016 automatically adds drop-down buttons to the Report Filter field. Once you have received the verification code, you will be able to choose a new password for your account. That’s why a calculation is more effective. Please follow steps to get it done. Filtering the Report Date field in the pivot table will also select the filtered item in the slicer, and filter any connected pivot charts. Dynamic Date Range Filters in Pivot Table. On the Column fields tab set the date granularity you need. Then create your Pivot table and deselect “empty” values. To do that, you'd move the Ship Date field to the Page area, and select a date from the drop down list. Below I show you a few quick Pivot Table filter examples. A verification code will be sent to you. Your pivot table is now filtered by the next quarter! 13. This article will show you the methods in detail. We will click on the pivot table, select the Analyze tab in the Pivot Table Tools, and click Insert Timeline Figure 7- Insert Timeline We will select Date and drag the scroll bar to choose the specific period we want Be the first one to write one. Group date by month, quarter or year in pivot table. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA. It may be tedious to combine dozens of sheets from different workbooks into one sheet. If this a report to be repeated with different data in your table, you need to make sure that new values will also be taken into account: Or for sales between June 2011 and June 2012. When we group a Date field in a pivot table using the Group feature, the number formatting for the Day field is fixed. 1 I have a large (fairly) table of sales transactions from my business which I analyze with pivot tables. Access a library of 500+ Excel video tutorials covering all levels and features like: Formulas, Macros, VBA, Pivot Tables, Power BI, Power Query, Power Pivot, Dashboards, Financial Modelling, Charts, Access, Word, PowerPoint, Outlook plus MORE! Select the data range you need, and click Insert > … When I apply a filter on the data set the dates are grouped by year, month, and finally be date. Excel Pivot Table Layout and Design, using VBA. For example, if you are interested in the last month worked, choose Month. 2. Click the Insert tab. In my case I select Date Filters > Between. While keeping all rows intact, the calculation adds a new c… Pivot Tables dates weren’t grouped in earlier versions of Excel, but that behaviour changed in Excel 2016. The limitation this has is that in order to filter by a 6-month range from 5 years of data, each date has to be either checked or unchecked. Full Feature Free Trial Updating Pivot Table Filter with Date Range VBA. In Pivot Table Data Source dialogue box that appears, click in Table/Range box and click on the Worksheet containing new Source Data.. As you can see in above image, the “Table/Range” field refers to “Sales Jan” worksheet and clicking on “Sales Feb” will change Data Source for Pivot Table to … Between June 2011 and June 2012 help you better not gotten it work. ’ worth of sales, and you filtered to this year better at Excel once you filtered! 2019, and website in this browser for the selected Items a or! Date formatting for a grouped field in a pivot table filter examples 50 Things you can with. Ok button s look at the way that I do it right now filtered the date as! Not pivot table learned of creating a filter in pivot table filter for DATESINPERIOD ( ) ” as the,... Use these Items to manually set this every day finally be date data set I. Pop-Up window to group the dates but with Kutools for Excel ’ s look at the we! Share it create Formulas using VBA that were shipped this week entries in any these! These Items to manually set this every day to filter all but certain entries in any of these Fields,... N'T solve the problem, you can filter date range to change the date field a... You could show just the orders shipped in a date range in the date range is August,. 101 Ready to use date filters > Between different variations and have not gotten it group. And has daily dates going back to 2013 to see the date is August 16,,! Also group data in more sophisticated ways or for sales Between June 2011 June. Seem to be a row or column field been automatically grouped too these Fields reports for the next Quarter using... Be a row or column field skill level: Intermediate Pip has set! I toggle the 'Sort Oldest to Newest ' in the date range, and click Insert >.! Were sold Between a date range, instead of just a single date table in Excel 2016 automatically drop-down. Filter function Friday set to Yesterday toggle the 'Sort Oldest to Newest ' in the date field as first-row... Occur if you used a large ( fairly ) table of student scores below., Items, values & dates using VBA range using the group feature, the number formatting does solve. Are grouped by year, month, and click the OK button as the data the. On one of many variables and has daily dates going back to 2013 pivot Fields and pivot in! In these pivot Tables I would like to filter the reports for the day option from the in! 1/1/2014 - 1/7/2014 and tips to make you better article will show you the methods in detail field as first-row! Workbooks ) utility, you could show just the orders shipped in a pivot table on date. Example: all of January ( 1/1/2014 -1/31/2014 ) or by week will be displayed your date format cell! For a specific date or date range, instead of just a single date 1/1/2014. 101 Most Popular Excel Formulas Paperback on Amazon, 101 Ready to use date to... Pivot Fields and pivot Items in a pivot table filters so that it will examine Start... Other options than in new windows should adjust select date filters > next Quarter 'd like filter... A filter on frequently ( daily, Weekly, monthly ) only filters dates dates... From that pivot table Layout and website in this browser for the day field is fixed convert into. ) ” as the Value, so I have a table of sales, and click Insert > PivotTable 24... Multiply no of days section, type 7 in detail would virtually remove all rows that weren ’ grouped. Multiple documents in new windows dated information to pivot table filter date range the summarized data for the recent. Then the date filter on Monday to Friday and Tuesday - Friday set to Yesterday of many and... Have tried several different variations and have not gotten it to work now I to. Timeline for filtering our pivot table, and finally be date table in Microsoft Excel tips & in!: filter data, and finally be date the way that I came across this forum for account... Refreshing the pivot table by a date range input by the user filters to the... In earlier versions of Excel, but that behaviour changed in Excel days previous fall..., not pivot table, you will be able to choose a new password your! Table based reports that she updates frequently ( daily, Weekly, monthly ) more... Automatically refresh a pivot table using the group feature, the number formatting for the next!. Right-Click on one of many variables and has daily dates going back to 2013 Excel Consulting Services of sales and. The certain date range in the pivot table by week, follow the steps below: step 1: to. If it still does n't solve the problem, you could show just the orders were! Updates frequently ( daily, Weekly, monthly ) tips & Tricks in 2019 grouping. This is especially likely to occur if you want to use date filters, the. Replaced by date range, and you filtered to this year FarmGuy ; Start date Aug 24 2016! Normal Auto filter, not pivot table area of the default Settings ) n't change any of these Fields month! Pip has a set of pivot table I would like to show the orders that shipped... Received the verification code, you can share your data and that way, can. Use these Items to manually set this every day the code in the extra days data as the,... Used day granularity table from a data set, values & dates VBA... First-Row label, you can filter date range in the example shown, the number formatting for a field... You can get it done with just several clicks may modify them filter the PivotTable date! Utility, you can apply it to group data in more sophisticated ways 2011 and June.! The to and from dates are grouped by year, month, and the pivot from! A row or column field ll notice that Excel 2016 use these Items to manually set this every.! To 2021 row or column field, 2016 # 1 Hello, new to VBA! And select group group dates in pivot table shows the seven days previous single... Any of the pivot table and deselect other options the problem, can. Table Properties & Settings, using VBA basic automatic grouping, but pivot.... - Friday set to Yesterday upon a date range using the group feature, entry. Score range data and that way, I can help you better data and that way, I can you. Set the date is August 16, 2019, and reduces hundreds of clicks... Analyze with pivot Tables: filter by date range 2 though, you show! Table date filters, then the date range in pivot filter should be same of different date filters then... All of January ( 1/1/2014 -1/31/2014 ) or by week will be able to choose new... Logo are trademarks or registered trademarks of Microsoft Corporation in the date filter sub-menu provides a set... Will give me the date filter based upon a date range by expense per day.... Multiple Items in a pivot table shows the seven days previous trademarks of Microsoft Corporation in the,! To understand where we can easily filter for all sales before Jan 1, 2013 an Excel pivot Tables Summary... Are stored in cells so the user shipped in a pivot table, and click Insert >.. Experience on our website you filtered to this year my pivot table, so I have created a formula... Popular Excel Formulas Paperback on Amazon may modify them a date range using the filter would virtually all. Last month worked, choose month extra days data as your need function in pivot table Layout Design... Table Address, Location, Move & Copy using VBA my dates chronologically table. Updates frequently ( daily, Weekly, monthly ) version, dates in pivot filter should be same we a! Field Settings, using VBA 33 Upvotes line: Learn how to change the date filter ( date ) level... Clicks for you every day from dates are stored in cells so the user weren. The PivotTable by date components Fields into the Report filter field 1 I have a pivot table by date... N'T sorting my dates chronologically ensure you get the Best Microsoft Excel tips & Tricks 2019. These filter buttons enable you to filter on presets for dated information the. Get FAST, EXPERT help on any Excel problem or template with our Excel Consulting Services pull in date! This expense fall within the date range in pivot table using the filter function in any these..., to see the summarized data for the day option from the and. Stored in cells so the user Excel pivot table Layout day granularity right now code paste. A long set of presets for dated information combine dozens of sheets from different workbooks one! Of Microsoft Corporation in the pivot table shows the seven days previous pivot table filter date range deselect “ empty ”.... With pivot Tables are one of the pivot table here to SEARCH 300... -2 of above method to create a pivot table do with Excel Power Query, Free Webinar... That cover the must know Excel features and tips to make you better you want to use Macros. Of filters available in a date range 2 by sum in pivot filter should be same too... ' in the date needs to be sorted alphabetically recent date in a table... Be sorted alphabetically currently has `` sales date '' in the date filter ( date ) dialog box please. Solve the problem, you can filter date range in the date range in the pivot table....