Apply different date filters to specific column values in a pivot table

tytds

New Member
Joined
Feb 2, 2022
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Suppose I have the following pivot table from one data source





The source data is essentially a spreadsheet of accounts which indicates 3 items (3 columns)- date account was created, date account was successful, date account was bought.

There are 3 stages an account goes through: step 1 it gets created. Step 2, if the employee deems the account to be successful, they mark it as successful and mark the date it became successful. If there is more interest in the account, the account is bought and the company receives money for it - they mark the date it was bought.

If you look at the data, the # of created accounts > # of successful accounts > # of bought accounts.

However, a created account can be made in Jan 2021 for example, but can be successful in Feb 2021 and then bought in June 2021. An account cannot necessarily be created, be successful, and be bought all in the same month/year.

That's the problem I have with this pivot table - the values field I have is using the count of date created, count of date successful and the columns on my pivot table is grouped by quarters of Y2021. The rows of the pivot table are the different regions the account belongs to. The problem I have is I am grouping the dates based on the date created and data is being filtered strictly from that date created filter; I don't know how to create a "global" date filter that is not strictly based on the date created filter

So, in the February 2021 column for example, it is telling me I have 8 created accounts and 6 successful accounts, when there really should be 5 successful accounts. If you double click that cell which launches a table of that data, you'll see an account that was successful in August 2021, however, it was created in February 2021, so that is why it will say 6 successful accounts . That is an example of a discrepancy in my pivot table. If region3 has 10 created accounts in January 2021 for example, and in the same month/year, has 5 successful accounts (by looking at the source data) and one account becomes successful in July 2021, the pivot table should indicate that correctly as 10 created accounts in Jan 2021 and 5 successful accounts in January 2021.
 

Attachments

  • edb9afcecb8ba2370bef7b2e1146b13d.png
    edb9afcecb8ba2370bef7b2e1146b13d.png
    19 KB · Views: 13

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Apply different date filters to specific column values in a pivot table
and Apply different date filters to specific column values in a pivot table

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,211,963
Messages
6,105,107
Members
447,947
Latest member
OX_2005

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
Back
Top