How to show accurate weekly data in Pivot Chart?

Hi All,

I have created a pivot chart to show certain data in weekly format, however I face some issues..
Please read below example:

The last day of February in 2017 (28 February 2017) fell on a Tuesday and under week 9.
The first day March in 2017 (1 March 2017) fell on the next day, Wednesday, and again under week 9.

The last day of March in 2017 (31 March 2017) fell on a Friday and under week 13.
The first day April in 2017 (1 April 2017) fell on the next day, Saturday, and again under week 13.

Now my issue is that if I look at the weekly view on my Pivot Chart, then because of the above, I see week 9 in both February and March and week 13 in both March and April - which also results in incorrect data display.

How can I correct this bug? Even if I select only the weeks on the slicer, the months are also shows and duplicate weeks (e.g. week 9 in both February and March and week 13 in both March and April).

Attached the sample excel and a screen shot on what bug I mean exactly.

Thanks a lot for any advice on how to fix this!

Originally Posted by dflak

I am not quite sure if this is what you want. First of all, I changed the data source to the Excel Table - only because tables know how big they are and they also copy down formulas.

I created a helper column called Week Ending with the formula: =[@Date]-WEEKDAY([@Date])+7 - this is the Saturday following @DATE.

Unlike the weekdays for a particular week number that can span across a month, a week end date must occur only in a single month. Then I redefined month to be associated with the week ending date =TEXT([@[Week Ending]],"mmm"). An example of the consequence is that the last week of June, Week 26, has a week end date of July 1. So week 26 is in July, even though 6 of the 7 of its days are in June.

If you take away the + 7 and replace it with + 1 you get the Sunday date that starts the week. You might want to work it that way.

Hi dflak,
No words again, you perfectly understood the issue and the solution perfectly solved it!
Thanks a lot again - you are awesome!

Originally Posted by dflak

I am not quite sure if this is what you want. First of all, I changed the data source to the Excel Table - only because tables know how big they are and they also copy down formulas.

I created a helper column called Week Ending with the formula: =[@Date]-WEEKDAY([@Date])+7 - this is the Saturday following @DATE.

Unlike the weekdays for a particular week number that can span across a month, a week end date must occur only in a single month. Then I redefined month to be associated with the week ending date =TEXT([@[Week Ending]],"mmm"). An example of the consequence is that the last week of June, Week 26, has a week end date of July 1. So week 26 is in July, even though 6 of the 7 of its days are in June.

If you take away the + 7 and replace it with + 1 you get the Sunday date that starts the week. You might want to work it that way.

Hi dflak,

I would like to ask for your help again with the week number issue.
Now that we entered 2018, I'm facing some issues with your solution above + another issue.
Let me explain:

2018 issue:
Data recorded for 31 Dec 2017, falls on the last week of 2017 (week 53), but because the week ending of week 53 is 6 January 2018, therefore on the Pivot Chart, the data shows for this day like it happened on week 53 in January 2017.
Please see screen shot as the first image and the yellow highlighted line on sheet MASTER DATA_excel table in the sample excel

Misrepresentation of data issue:
The last days of January 2018 (28-31) fall on the first week of February 2018 (week 5). But because of this, this data is also showing on week 5 (February 2018) on the chart. Which, if I put the cursor over that line in the chart, show that those data belongs to February 2018 (week 5), whereas it belongs to January 2018 (week 5).
Please see screen shot as the second image and the orange highlighted line on sheet MASTER DATA_excel table in the sample excel

Is it possible to fix these? I was playing around with many tricks - like using the month of the original date (column A) and not the month of the weekending date (column L), but doesn't work.

Thank you in advance if you can help in this!

Originally Posted by dflak

I had the month defined in terms of Week Ending. I changed it to be defined in terms of the actual date. Check to see if this is what you want.

Thank you dflak!
I tried this as well. It solves both issues, but unfortunately it also throws me back to my initial issue - where the last days of a given month and the first days of the next month falls on the same week number, therefore split on the pivot chart and shows the data inaccurately.
Please see the screen shot from your file. The last days of November and the first days of December fall on week 48 and the data is split.

I tried all kinds of IF condition, but I always face an issue. I have a fear only VB could solve this :|

Hi dflak,
Any luck with this? (please see my reply to your comment above).
I'm constantly researching and trying different things, but I'm always back to square one

Or if anyone else has an idea how to solve this issue, please, don't hold yourself back

Thanks for the feedback dflak!
I will follow your suggestion and post a new thread and cross reference as well.

Thank you!!