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://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).
Note that COUNT(Table1[CustomerID]) would return the same result.
This measure returns the number of unique customers.
=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.