Date Filters / "This Year" on Jan 1

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
308
Office Version
  1. 2019
Platform
  1. Windows
Helo,
When using Date Filters in a Power Query to be used in a Pivot Table (a Pivot Table which will always show monthly totals, I selected the date filter "This Year", which works fine for me right now. But I started thinking, what about on January 1? The correct data that I need will be filtered out before it hits the Pivot Table. I suppose I could just not use that filter at all, but then I'm thinking I'll end up seeing 2 January columns. I have Index and vlookup formulas referencing the month names in that table, so that will cause problems. How can I avoid this? Thx
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,988
there's only 1 january for the current year. Why would you get 2 januarys?
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
308
Office Version
  1. 2019
Platform
  1. Windows
Hi Ranman256Because as it stands with the "this year" date filter, the query will pull the info from this 2021's months. But if I wake up on January 1 and go to run the numbers and get all the final reporting for 2021 (including the final December monthly report which will then include the final day of December 31st), won't the "this year" filter on (Jan 1) then filter out 2021. Beacause at that point, 2021 is not "this year", 2022 is. So I was saying, that's why I stopped and turned that "this year" date filter off. BUT, it also seems to me that turning off the year-related filter would result in there soon being 2 Januarys (2021 & 2022, then 2 Februarys, etc. Is that right? or no? Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,781
Messages
5,655,253
Members
418,183
Latest member
skaufman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top