A Generalized “New Customers in Time Period” Solution, Inspired by Tuesday’s Post
David Hager’s post on Tuesday really planted a seed in my brain. And then a comment on that post from Charlie got me thinking further.
How can we extend the “New Customers per Day” concept to become “New Customers in <Any Period of Time>?” New Customers per Month for instance.
Step One: Make That Calendar Table!
I think it’s a general habit we should all get into: if you’re using the Time Intelligence functions in DAX (like DATESBETWEEN), it is going to be MUCH safer all the way around if you create a separate Calendar/Date table first.
Quick technique for doing this is to use a flat pivot:
Create a Flat Pivot, Use All Date-Related Columns from Your Original Table on Rows,
Turn Off Subtotals, then Copy the Pivot
Paste as New Table in the PowerPivot Window
Calendar Table in PowerPivot
Which I then relate to my original Table1…
Step Two: Measures!
[PreExisting Customers] =
The first measure is just a distinct count of customer ID, and the second one calculates how many such disctinct customers “existed” from date 0 (the beginning of time, heh heh) up through the last day before the selected time period.
(Note how we do not need to use the ALL function in the second measure, unlike in Tuesday’s post. This is because of the separate Calendar table we’re using – but that’s not the only benefit of the separate Calendar table).
So if I am looking at March 2009 on my pivot, FIRSTDATE(Calendar[Date]) returns March 1, 2009 and then I subtract 1 to get Feb 28, 2009 – the last day before the selected month started.
Here it is:
The number of pre-existing customers can never go DOWN over time, so the fact that this shows a nice smooth increase is a good sign.
Now I add another measure:
[Customers Life to Date] =
It’s just like the [PreExisting Customers] measure except for that highlighted portion – we extend up through the last date in the selected time period.
And then our last measure:
[New Customers in Period] =
[Customers Life to Date] – [PreExisting Customers]
If I want to use Days or Years on rows instead, I just rearrange the pivot, and the formulas still work:
Or, say, Year:
Always want to emphasize this as a HUGE benefit of PowerPivot: PowerPivot formulas are portable. They adapt to new report shapes with no additional effort.