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

By **David Hager**

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.

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

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?

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.

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

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?