Existing customer base- changing the base in powerpivot

KnifeandFork

New Member
Joined
Jan 9, 2014
Messages
5
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 CustomersNew Customers Lost CustomersNew Customers Lost CustomersNew Customers
174(97)236(117)143(176)240

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


Thanks in advance!!
 

KnifeandFork

New Member
Joined
Jan 9, 2014
Messages
5
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 LabelsRetain CustomersSum of SalesSales Q AgoRetain CustomersSum of SalesSales Q AgoRetain CustomersSum of SalesSales Q AgoRetain CustomersSum of SalesSales Q Ago
D480005028203,9693,969128,622128,622459,149459,149243,858
D4800051504151,126185,729185,729101,147101,14773,22873,22887,622
D4800052562247,06063,93463,934187,745187,745

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

Thanks...
 
Last edited:

Forum statistics

Threads
1,085,543
Messages
5,384,338
Members
401,887
Latest member
Somesh

Some videos you may like

This Week's Hot Topics

Top