I have a pivot table report that uses data pulled from a database through VBA and placed on a sheet that is the source of the pivot table data. I've been using this method for years and it works well.
The current Sales report spans a few years if the user wishes (date range is dynamic for each run). The data has a column for Invoice date and Fiscal year. Fiscal year for a company can be any date range but in this case is Dec-Nov. Showing sales figure columns for fiscal year is easy so the owner of the company can see annual sales for each customer (rows).
When users typically ask for YTD reports they run them from one date over a few years in question and ending usually at the end of the current month. If they wish to show only the fiscal year to the current month for each year then they filter only for the months required based on Invoice Date.
eg. Date range 12/1/2019 to 6/30/2022. This will result in three columns for the years 2020, 2021 and 2022 with 2022 going from Dec/21 to Jun/22 including the full month.
My client has now informed me that if today is Jun 15 then he would like the YTD reporting to end Jun 15. There is no problem here for 2022 because the end date of the pulling of the raw data has a start and end date so he can just enter Jun 15/22 instead of Jun 30/22. However, the complication came up when he said and I want each year to end Jun 15.
I know I can parse the date range and modify the WHERE statement in my VBA SQL statement. What I am trying to figure out is if there is any way for the filtering in a pivot table to be able to do this. Each year from 2020 should be able to display only Dec 1/19 to Jun 15/20, Dec 1/20 to Jun 15/21 and Dec 1/21 to Jun 15/22 as the three columns.
Is there any way to handle this directly in the pivot table using a multi-year date range for the raw data or should I modify my WHERE clause to narrow down the raw data for the report?
TIA, rasinc.
The current Sales report spans a few years if the user wishes (date range is dynamic for each run). The data has a column for Invoice date and Fiscal year. Fiscal year for a company can be any date range but in this case is Dec-Nov. Showing sales figure columns for fiscal year is easy so the owner of the company can see annual sales for each customer (rows).
When users typically ask for YTD reports they run them from one date over a few years in question and ending usually at the end of the current month. If they wish to show only the fiscal year to the current month for each year then they filter only for the months required based on Invoice Date.
eg. Date range 12/1/2019 to 6/30/2022. This will result in three columns for the years 2020, 2021 and 2022 with 2022 going from Dec/21 to Jun/22 including the full month.
My client has now informed me that if today is Jun 15 then he would like the YTD reporting to end Jun 15. There is no problem here for 2022 because the end date of the pulling of the raw data has a start and end date so he can just enter Jun 15/22 instead of Jun 30/22. However, the complication came up when he said and I want each year to end Jun 15.
I know I can parse the date range and modify the WHERE statement in my VBA SQL statement. What I am trying to figure out is if there is any way for the filtering in a pivot table to be able to do this. Each year from 2020 should be able to display only Dec 1/19 to Jun 15/20, Dec 1/20 to Jun 15/21 and Dec 1/21 to Jun 15/22 as the three columns.
Is there any way to handle this directly in the pivot table using a multi-year date range for the raw data or should I modify my WHERE clause to narrow down the raw data for the report?
TIA, rasinc.
