How can I in a pivot filter detail to show new accounts in current month with sales where all other months show no sales?

Question

I have a pivot table which is huge with raw data has more than 800,000 lines. I have customers in the row labels and month or year in the column labels.

Each month this pivot tablr grows adding on the previous month of sales data reporting, e.g. I just finished compiling and reporting February sales, so I have all of 2012, 2013 and now January and February of 2014.

From this pivot table, I need to pull out any account in the current month having sales where all other months have none. I'm struggling on how to achieve this in the pivot table. So I'm thinking I need to find a formula that yields a Y/N for new or not in a column of the raw data.

Can anyone help or advise how this can be accomplished? I'm more than willing to also send a sample of data so you can see how my pivot is laid out and how the raw data looks as well.

Answer 1

As you suspected, a "helper" column is needed. Assuming your data looks something like this:

I added column D, which I called "New Customer?" containing this formula in D2 and filled down: =IF(SUMIFS($C$2:$C$8,$A$2:$A$8,A2,$B$2:$B$8,"<"&DATE(YEAR(TODAY()),MONTH(TODAY()),1))=0,"Y","N")

This figures out the total prior month sales for the unique customer name/number, and if it is ZERO, then sets this flag to "Y"/yes.

From here, we can put this as a report filter in our pivot, to get customers without prior month sales only: