Hi folks.  Today we are fortunate to have a guest post from David Hager.  He explains a technique for counting how many new customers are acquired or “seen” each day.  (I’m going to think about whether this has web site traffic analysis uses as well – New Visitor vs. Returning Visitor sort of stuff).

***UPDATE:  Inspired by David’s work, I extended this technique to cover per Month, Year, Week, etc.:  http://www.powerpivotpro.com/2013/01/new-customers-per-day-generalized-to-new-customers-per-month-etc/

### Count of New Customers per Day in PowerPivot

Information vital to any company is being able to identify customer patterns. Counting how many new customers per day a company acquires is perhaps the most important data that can be obtained. The following model will show how this can be done with DAX measures in PowerPivot. For comparison, two other measures are included in the Pivot Table (shown in Figure 1).

TotalCustomersPerDay:

=COUNTROWS(Table1)

Note that COUNT(Table1[CustomerID]) would return the same result.

DistinctCustomersPerDay:

=DISTINCTCOUNT(Table1[CustomerID])

This measure returns the number of unique customers.

NewCustomersPerDay:

=CALCULATE([DistinctCustomersPerDay],DATESBETWEEN(Table1[Date], BLANK(),LASTDATE(Table1[Date])), All(Table1[Date]))
CALCULATE([DistinctCustomersPerDay],DATESBETWEEN(Table1[Date], BLANK(),LASTDATE(Table1[Date])-1), All(Table1[Date]))

This formula shows the real power of DAX. The first part of the formula (highlighted in green) returns the running total of the DistinctCustomersPerDay measure. The second part of the formula (highlighted in yellow) returns the running total of the DistinctCustomersPerDay measure up to the previous day of the pivot table row context. The difference affords the number of new customers per day.

(Note from Rob:  in this case, we “get away with” using a time intelligence function without a separate Calendar/Date table, which is usually a requirement.  For space reasons we are omitting that here.)

Figure 1

Note in particular the values for 1/5/2009, 01/08/2009 and 01/12/2009 for Figure 1. Compare those with the corresponding values in Figure 2 (PowerPivot Window) in order to see how the expected results were returned.

Figure 2

Now, imagine a company that has thousands of transactions each day. The capability of PowerPivot and DAX to easily provide a record of new customers per day using this methodology once again shows the power of this new BI tool. And, thanks again Rob for providing a forum for the sharing of PowerPivot solutions.

You can download the sample file here.

#### Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

## This Post Has 8 Comments

1. I am wondering why you have labeled your measures ‘PerDay” when there is no time component to the calculation – for example does TotalCustomersPerDay just return the TotalCustomers in the table regardless of day? This may be just semantics, but it may also confuse some readers.

Also I am wondering if the DatesBetween function could be called once rather than twice – to just calculate the totals between LastDay -1 and LastDay – would that work?

Thanks

1. powerpivotpro says:

Charlie, the way David wrote the measure, it actually is “per day” since it uses LASTDATE()-1 to go back one day. It wouldn’t show you the right number of new customers per month if you put Month on rows of the pivot instead of days. I’m going to look at your other question while I write today’s post.

2. I should have been more explicit – the final measure is PerDay as it has the time elements in it, but his previous measures that he is using to build towards the final one do not – my example was his first measure which is called TotalCustomersPerDay =COUNTROWS(Table1) – this I found confusing in that there is no date component to the calculation – I agree if his pivot table was set to report daily, it would work, but I wondered if the pivot table was set to report monthly whether this measure would produce a PerDay number or a PerMonth number?

3. matt says:

Could this method be expanded on to give a measure of staff retention, given a large casual workforce you could measure average turnover cycles etc? Or given enough data predict high churn periods.

1. powerpivotpro says:

Yes I believe it’s the same thing, just in reverse – who was here before but is no longer showing up? You’d likely need some sort of definitive way of tracking new hires though, since otherwise if you hire one new person and lose one person, it might look like no change. Haven’t thought too deeply about this though.

Also consider the more generalized technique:

http://www.powerpivotpro.com/2013/01/new-customers-per-day-generalized-to-new-customers-per-month-etc/

4. Arjun says:

Hi Everyone,

Can we do this using a Standard Pivot Table by any way? I tried this but came to know that we need a either a helper column with a powerful formula or a calculated field to capture this. But I feel the same inefficiency leads to the use of PowerPivot.

Arjun

5. Daniel Maycock says:

I’m trying to get cume records up to a given month, and used the formula above, but am only getting the total for the month itself vs the cume total. What might I be doing wrong?