Need Help with pivot table and week numbers

[Sineadd]

Hi

I am trying to get pivot table to show me sales data for a particular store for a each week number of the year.
I have my main data in the below format

Date	Month	Week No	Day	MMM-DD	Warehouse	Store	Sales Values
30/12/2019​	Dec	1​	30​	Dec-30	West	Shop 1	20​
31/12/2019​	Dec	1​	31​	Dec-31	West	Shop 1	20​
01/01/2020​	Jan	1​	1​	Jan-01	West	Shop 1	20​
02/01/2020​	Jan	1​	2​	Jan-02	West	Shop 1	20​
03/01/2020​	Jan	1​	3​	Jan-03	West	Shop 1	20​
04/01/2020​	Jan	1​	4​	Jan-04	West	Shop 1	20​
05/01/2020​	Jan	1​	5​	Jan-05	West	Shop 1	20​
06/01/2020​	Jan	2​	6​	Jan-06	West	Shop 1	1​
07/01/2020​	Jan	2​	7​	Jan-07	West	Shop 1	1​
08/01/2020​	Jan	2​	8​	Jan-08	West	Shop 1	1​
09/01/2020​	Jan	2​	9​	Jan-09	West	Shop 1	1​
10/01/2020​	Jan	2​	10​	Jan-10	West	Shop 1	1​
11/01/2020​	Jan	2​	11​	Jan - 11	West	Shop 1	1​
12/01/2020​	Jan	2​	12​	Jan -12	West	Shop 1	1​

As week 1 of 2020 starts on the 30/12/2019 it doesn't display correct figures for week 1 when I create pivot table like below.
Can anyone please help me and let me know what I need to do so that it will display the correct week 1 figures for week 1 2019 - Weeks run from Monday - Sunday

Row Labels	1​	2​	total
2019
Shop 1	100​	7​	107​
total	100​	7​	107​

 

[cyrilbrd]

Like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
16	Date	Month	Week No	Day	MMM-DD	Warehouse	Store	Sales Values	year		Sum of Sales Values		Store
17	12/30/2019	Dec	1	30	12/30/2020	West	Shop 1	20	2019		year	Week No	Shop 1	Grand Total
18	12/31/2019	Dec	1	31	12/31/2020	West	Shop 1	20	2019		2019	1	40	40
19	01/01/2020	Jan	1	1	01/01/2020	West	Shop 1	20	2020		2019 Total		40	40
20	01/02/2020	Jan	1	2	01/02/2020	West	Shop 1	20	2020		2020	1	100	100
21	01/03/2020	Jan	1	3	01/03/2020	West	Shop 1	20	2020		2020	2	7	7
22	01/04/2020	Jan	1	4	01/04/2020	West	Shop 1	20	2020		2020 Total		107	107
23	01/05/2020	Jan	1	5	01/05/2020	West	Shop 1	20	2020		Grand Total		147	147
24	01/06/2020	Jan	2	6	01/06/2020	West	Shop 1	1	2020
25	01/07/2020	Jan	2	7	01/07/2020	West	Shop 1	1	2020
26	01/08/2020	Jan	2	8	01/08/2020	West	Shop 1	1	2020
27	01/09/2020	Jan	2	9	01/09/2020	West	Shop 1	1	2020
28	01/10/2020	Jan	2	10	01/10/2020	West	Shop 1	1	2020
29	01/11/2020	Jan	2	11	01/11/2020	West	Shop 1	1	2020
30	01/12/2020	Jan	2	12	01/12/2020	West	Shop 1	1	2020
Sineadd

Cell Formulas
Range	Formula
I17:I30	I17	=YEAR([@Date])

 

[cyrilbrd]

Or based on your presentation:

Book1
	K	L	M	N	O
16	Sum of Sales Values		Week No
17	year	Store	1	2	Grand Total
18	2019	Shop 1	40		40
19	2019 Total		40		40
20	2020	Shop 1	100	7	107
21	2020 Total		100	7	107
22	Grand Total		140	7	147
Sineadd

or streamlined:

Book1
	K	L	M	N	O
16	Sum of Sales Values		Week No
17	year	Store	1	2	Grand Total
18	2019	Shop 1	40		40
19	2020	Shop 1	100	7	107
20	Grand Total		140	7	147
Sineadd

 

[sandy666]

with Power Query

is that what you want?

 

[Sineadd]

Or based on your presentation:

Book1
	K	L	M	N	O
16	Sum of Sales Values		Week No
17	year	Store	1	2	Grand Total
18	2019	Shop 1	40		40
19	2019 Total		40		40
20	2020	Shop 1	100	7	107
21	2020 Total		100	7	107
22	Grand Total		140	7	147
Sineadd

or streamlined:

Book1
	K	L	M	N	O
16	Sum of Sales Values		Week No
17	year	Store	1	2	Grand Total
18	2019	Shop 1	40		40
19	2020	Shop 1	100	7	107
20	Grand Total		140	7	147
Sineadd

Week 1 should show the result 140 for sales values as week one is from the 30/12/2019-5/01/2020

Having the pivot table to display the correct amount for each week will work no problem for all other weeks and it will only happen if week 1 includes dates from previous year or if week 52 runs into dates for the following year.

I had originally inserted an extra column in the data table with the heading year and manually changed the 30th and 31st Dec 2019 to the year 2020 and that will then display the correct data in week numbers for me but I was hoping there was an easier way than having to manually change the year for dates that are in the previous year but count as week 1 in the current year.

 

[sandy666]

or

or

 

[Sineadd]

with Power Query
View attachment 5986
is that what you want?

with Power Query
View attachment 5986
is that what you want?

Hi

Thanks for the reply

Im looking to get the format as below but with week 1 would equal 140 going by the data entered for the dates above 30th Dec 2019 - 5th Jan 2020

I got it to display as below by inserting an extra column with year in the main data table and manually changing the year for the dec dates to 2020 so it would pull those figures as week 1 2020 but i was hoping there was an easier way beside manually updating the year for certain dates as it will happen most years for week 1 / week 52 / week 53

 

[sandy666]

like this?

warehouse column and store column can be removed if necessary

 

[cyrilbrd]

Week 1 should show the result 140 for sales values as week one is from the 30/12/2019-5/01/2020
...

In the Pt "streamlined" week one is 140.
Week 2 is 7.