Two date counts in one line graph - showing flow of cases received and completed

**MissScotland** · 05-01-2023, 08:52 AM

Hello. I am trying and failing to create something for work.

I have one table with data of my work cases. The goal is to have a line chart which shows an inflow and outflow of cases: cases received and cases completed by week, with the option to use a slicer (to look at specific topics) and timeline (to look at quarters/months).

I have attached my attempts using a similar but fake spreadsheet.

Excel help.xlsx

You can see my 1st Attempt was using the Tracker table and PivotTable. Did not work out as it doesn't show a case can be received and completed in different weeks.

For 2nd Attempt, I've tried to use Relationships. I have never used this before. I can filter by Topic but it only changes one of the lines on the chart/table in 2nd Attempt sheet.

I have no idea if I'm even on the right track for presenting this kind of data. There might be an easier way to do it, or another way that makes more sense... looking for some advice, some help, some guidance. I simply don't know enough about Excel to troubleshoot this myself.

I hope that makes sense but please let me know if you have any questions.

**JeteMc** · 05-03-2023, 10:48 PM

Hello MissScotland and Welcome to Excel Forum.
As you mentioned 'relationships' I am guessing that you have Excel 2016 or newer. (Please update your profile)
This proposal employs power query to produce most of the green/white table on the Tracker sheet using the following M code:

Please Login or Register  to view this content.

The Year and Week columns are added later using: =YEAR([@Date]) and =WEEKNUM([@Date]) respectively.
The pivot table on that sheet is produced from the green/white table and then the graph is produced from the pivot table.
In my opinion, a column graph is easier to understand than a line graph for this application, but it can be easily changed if that is your preference.
Let us know if you have any questions.