I currently have a table
[Date][Weekday][Store][Count]
with dates that start at 06/01/2020 and go to 06/30/2022
I'd like to create a year over year comparison of each count by weekday for each year. Each week goes from Monday to Saturday, and skips Sunday.
For example
| 6/1/2020 | Mon | South | 30 |
| 6/2/2020 | Tue | South | 29 |
| 6/3/2020 | Wed | South | 34 |
| 6/4/2020 | Thu | South | 35 |
| 6/5/2020 | Fri | South | 29 |
| 6/6/2020 | Sat | South | 28 |
| 6/8/2020 | Mon | South | 30 |
notice how it skips sunday and goes back to Monday,
also, if we were comparing 06/01/2020's monday to 2021, it would be monday 06/07/2021 and for 2022, it would be monday 06/06/2022
Trying to understand the best way to go about this
Have you tried a regular pivot table? If you could upload an Excel file containing a bit more than just one week of data, it would be easier to demonstrate.
Here's an attachment. Now I have a bit of a different approach/request.
It's column format is now
[Weekday][Count][Date Range][Store]
You'll see in the data there are two ranges, one for 2020 and one for 2021
And I'm now wondering how I could create a pivot table that shows the counts side by side for each range and the difference between them
(maybe even using a year over year calculation where you subtract last year's count from the next years count, and divide that remainder by last year's count)
Thanks for the file. Since you already started in PQ, I continued there. Though, your file connects to data on your PC and I can not work with with your query. If you can get to the stage that I created in the blue table, then you can follow the code that I created on that one, producing the green table in O1.
