Existing customer base- changing the base in powerpivot

[KnifeandFork]

Hi- thanks for helping. I haven't been able to find a solution for this and have been pulling my hair out for days.

I have a great big powerpivot table with all our customer sales by customer by quarter for the past couple of years. I want to be able to see how our customer base has evolved over time- taking customer base to mean 'customers that have bought at least once in the P12M'. Imagine a little waterfall chart with customer base 2 years ago, gained and lost customers by quarter, and customer base today.

The issue I'm having is that when I insert quarters into the pivot table, all distinct count formulas apply only to the customers who bought that quarter. So basically, if try a distinct count with a filter for customers who have sales P12M=0, I get zero returned, because obviously if they haven't bought, they're not counted this quarter.

For lost customers, I was able to fix this by using =calculate(DISTINCTCOUNT(Sales_Data[Customer Number]),PARALLELPERIOD(QuarterRef[Date],-12,Month),Sales_Data[Sales Next12M]<1, Sales_Data[Sales]>0)

Basically- I went 'back in time' a year with parallel period and counted forwards to this quarter to see how many repurchased, that way taking last year's customer base instead of this one.

My issue is now for my 'customer base'- ie, customers that have bought at least once in the past year, but not necessarily this quarter. If I take a calculate(distinctcount(etc....), it takes this quarter's base, which is much smaller than this year's base.... If I go back in time and count forwards, I only take last year's customer base, not any new customers we've acquired in the last year.

Any idea how to get PowerPivot to take 4 quarters customer lists as a base and filter on that, keeping the quarter variable in the columns so I can build my waterfall?

Basically adding 'existing customers' to this, taking 'existing' to mean 'bought at any point during the last 4 quarters'. It's an intersection of the 4 customer lists.....

Column Labels
Q		Q Ago		2Q Ago		3Q Ago
New Customers	Lost Customers	New Customers	Lost Customers	New Customers	Lost Customers	New Customers
174	(97)	236	(117)	143	(176)	240

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Thanks in advance!!

 

[KnifeandFork]

So, I feel I may be getting close, but I still can't do it... I've created four measures: Sales Q Ago, Sales 2Q Ago etc... with parallel period. I'm trying to use those measures to filter the distinct count:

=CALCULATE(DISTINCTCOUNT(Sales_Data[Customer Number]),FILTER(SALES_Data,SALES_Data[Sales Q Ago]>0||SALES_Data[Sales 2Q Ago]>0||SALES_Data[Sales 3Q Ago]>0))

Now I get 0 as a result.... Any ideas? The Sales Q Ago measures work fine and return the right result:

	Column Labels
	Q			Q Ago			2Q Ago			3Q Ago
Row Labels	Retain Customers	Sum of Sales	Sales Q Ago	Retain Customers	Sum of Sales	Sales Q Ago	Retain Customers	Sum of Sales	Sales Q Ago	Retain Customers	Sum of Sales	Sales Q Ago
D4800050282	0	3,969		3,969	128,622		128,622	459,149		459,149	243,858
D4800051504	151,126	185,729		185,729	101,147		101,147	73,228		73,228	87,622
D4800052562	247,060	63,934		63,934	187,745		187,745

<colgroup><col span="13"></colgroup><tbody>
</tbody>

Thanks...