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.: https://p3adaptive.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.
