Pivot table is unable to count different date ranges

Massy

New Member
Joined
Apr 26, 2017
Messages
32
Hi all,

I've tried to search for the solution but was unable to find it, hence I hope you folks can help.

I have created a pivot table where I have two columns - Date Received and it contains dates when the item was received and then I have a column Date Completed and it contains the dates when item was completed. I need to create a graph that shows the trend of Date received vs Date Completed - a simple column bar chart. The graph needs to show Monthly Break down for each year i.e.:
2020
Jan - 5
Feb - 7
etc
2021
Jan - 10
Feb - 11
etc

Getting the count for 'Date Received' is straight forward - pull the Date Received field into Rows area, keep Years and then do same for Count in Values. This is where I get stuck, if I pull Date Completed count into Values, the count it shows does not show for the Date Completed dates, but for the Date Received date window. This is, i'm guessing, because Date Received is only field in Rows, I tried to move Date Completed to Rows, but that messes it all up and still shows unnecessary counts for Date Completed. If I try to move any of the fields into Column area then it won't give me monthly breakdown anymore but every single date break down - so table becomes huge.

I tried to create two separate pivot tables, which work fine and show data I need, except now I can't get a graph out of it because the pivot table fields are dynamic, they grow as new dates get added - Date received items won't always have completion date yet. I tried creating manual tables that reflect both pivot tables, but because they are dynamic - it didn't work, tried few other options still nothing. So now I am stuck.

All I need is a count for number of items for Date Received and Date Completed and then have a graph represent that.

Any help would be greatly appreciated!
Thank you
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,141,771
Messages
5,708,441
Members
421,570
Latest member
BaileyJ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top