Pivot Chart arrangement needed

[rini]

Hi there,
I am trying to represent detailed datewise performance for a month in 2017 for same time 2016.
I am not able to create them directly using pivot chart.
I have attached raw file in screenshot1 in the word file attached.
I have to create data for july 2016 and july 2017 individually as in screenshot 2 and 3.
And then in I have to combine them as in screenshot 4 to get the graphical view that I have represented.
I want dates on any one axis vs rev for 2016 and 2017 on other directly from a pivot chart. I also want to shade weekday and weekend with different color on the graph.

Please suggest how to do it directly from my raw file

 

[Hui]

I would add a field to the original data which had the Month and Day
eg: =text(date field,"mmdd")
then simply use that as a grouping field in the pivot table

 

[Hui]

Posting real data is much more preferable to images and you will get a much more specific and targetted answer

 

[rini]

Hi,
I tried what you shared but output doesn't help me.

 

[Hui]

You are so close

Change the Chart to a Bar Chart and change the fields as shown


or see attached file:

 

[rini]

Sorry it doesn't help my team understand which date in a month this year vs last year gave us good business.
Kind of real dates ( not mmdd format as its confusing) on x axis and 2016/2017 rev represented on y axis in line chart format is what they want.

 

[Chihiro]

Do you have access to PowerPivot? If you do, this sort of analysis is exactly what it's meant to do.

If not, best method can vary depending on version of Excel.

 

[rini]

I don't have powerpivot in my excel 2016 version. sorry

 

[Hui]

 

[rini]

Awesome thanks a ton