Year on Year Line Chart

Here's how I did it. I'm not sure if I like it, but it was really quick and easy to do.

1) Select a cell in the raw data -> Insert -> Pivot Table
2) Drag "Date" to the row labels field and "Data A" to the sum values field.
3) Select a cell in the pivot tables "row columns" column -> Pivot table tools -> options -> Grouping selection (or right click -> Group). Select Days and Years for the groups. Note the addition of "years" to the available fields in the Pivot table field list.
4) Drag the newly appeared "years" to the column labels field.
5) Select the pivot table (if needed) and insert -> line chart.

There you have a year over year line chart. Does that work for you?

I don't know why your version of Excel combined 2017 and 2018 for the later months like it did. When I create from scratch, my 2018 line terminates at the "blanks" in the later months of 2018.

I am sure there is a different, "more visually appealing" way to do this. It probably depends on what you think is going to be more visually appealing. What do you have in mind?

Off the top of my head, the approach to something that doesn't go through a pivot table would involve some helper columns on the raw data page. A YEARFRAC() function to convert the date serial numbers to fraction of a year. Then an IF() function to place the data into year based columns. In an approach like this, D2 =YEARFRAC(DATE(YEAR(A2),1,1),A2)
E2=IF(YEAR(A2)=E$1,$B2,NA()) where E1 contains 2017.
Copy E2 into F2 and place 2018 into F1.
Copy D2:F2 down as far as needed.
Use these helper columns to create a scatter chart.

This should look exactly the same as the pivot table/chart approach (with different values on the X axis). As noted, I don't know why your pivot table chose to blend 2018 and 2017 when 2018 did not have data. Again, making this more "visually appealing" depends on what you think will make this more visually appealing.

I am not an expert on pivot tables, but it seems like it should be the same basic idea. It is not clear to me if you are wanting multiple pivot tables/charts for each data type or if you are trying to combine this all into a single chart. About all I can suggest at this point is to create a few pivot tables/charts and try different things to see what you can get them to do.

First issue, is that you have stacked line chart and not line chart.

Here's how to clean it up...

1. Delete your current chart.

2. Refresh Pivot Table.

3. Recreate Days & Years grouping. Make sure you group it in one shot for both. This will reset Years from column to row field.

4. Move Years back to column field.

5. Add PivotChart, making sure that you select line chart and not the stacked line chart.

See attached.