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.....
[TABLE="width: 838"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Column Labels[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Q[/TD]
[TD] [/TD]
[TD]Q Ago[/TD]
[TD] [/TD]
[TD]2Q Ago[/TD]
[TD] [/TD]
[TD]3Q Ago[/TD]
[/TR]
[TR]
[TD]New Customers [/TD]
[TD]Lost Customers[/TD]
[TD]New Customers [/TD]
[TD]Lost Customers[/TD]
[TD]New Customers [/TD]
[TD]Lost Customers[/TD]
[TD]New Customers [/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD="align: right"](97)[/TD]
[TD="align: right"]236[/TD]
[TD="align: right"](117)[/TD]
[TD="align: right"]143[/TD]
[TD="align: right"](176)[/TD]
[TD="align: right"]240[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 1040"]
<colgroup><col span="13"></colgroup><tbody>[TR]
[TD] [/TD]
[TD="colspan: 2"]Column Labels[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Q[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Q Ago[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2Q Ago[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3Q Ago[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Retain Customers[/TD]
[TD]Sum of Sales[/TD]
[TD]Sales Q Ago[/TD]
[TD]Retain Customers[/TD]
[TD]Sum of Sales[/TD]
[TD]Sales Q Ago[/TD]
[TD]Retain Customers[/TD]
[TD]Sum of Sales[/TD]
[TD]Sales Q Ago[/TD]
[TD]Retain Customers[/TD]
[TD]Sum of Sales[/TD]
[TD]Sales Q Ago[/TD]
[/TR]
[TR]
[TD="colspan: 2"]D4800050282[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3,969[/TD]
[TD][/TD]
[TD="align: right"]3,969[/TD]
[TD="align: right"]128,622[/TD]
[TD][/TD]
[TD="align: right"]128,622[/TD]
[TD="align: right"]459,149[/TD]
[TD][/TD]
[TD="align: right"]459,149[/TD]
[TD="align: right"]243,858[/TD]
[/TR]
[TR]
[TD="colspan: 2"]D4800051504[/TD]
[TD="align: right"]151,126[/TD]
[TD="align: right"]185,729[/TD]
[TD][/TD]
[TD="align: right"]185,729[/TD]
[TD="align: right"]101,147[/TD]
[TD][/TD]
[TD="align: right"]101,147[/TD]
[TD="align: right"]73,228[/TD]
[TD][/TD]
[TD="align: right"]73,228[/TD]
[TD="align: right"]87,622[/TD]
[/TR]
[TR]
[TD="colspan: 2"]D4800052562[/TD]
[TD="align: right"]247,060[/TD]
[TD="align: right"]63,934[/TD]
[TD][/TD]
[TD="align: right"]63,934[/TD]
[TD="align: right"]187,745[/TD]
[TD][/TD]
[TD="align: right"]187,745[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks...