Calculating the number of new ID numbers per month in powerpivot

Question

My dataset provides a monthly snapshot of customer accounts. Below is a very simplified version:

Date_ID | Acc_ID
------- | -------
20160430| 1
20160430| 2
20160430| 3
20160531| 1
20160531| 2
20160531| 3
20160531| 4
20160531| 5
20160531| 6
20160531| 7
20160630| 4
20160630| 5
20160630| 6
20160630| 7
20160630| 8

Customers can open or close their accounts, and I want to calculate the number of 'new' customers every month. The number of 'exited' customers will also be helpful if this is possible.

So in the above example, I should get the following result:

Month   | New Customers
------- | -------
20160430| 3
20160531| 4
20160630| 1

Basically I want to compare distinct account numbers in the selected and previous month, any that exist in the selected month and not previous are new members, any that were there last month and not in the selected are exited.

I've searched but I can't seem to find any similar problems, and I hardly know where to start myself - I've tried using CALCULATE and FILTER along with DATEADD to filter the data to get two months, and then count the unique values. My PowerPivot skills aren't up to scratch to solve this on my own however!

It's hard to understand the data you pasted, do you think you could reformat it? — Charlie, Oct 18 '16 at 02:30

score 0 · Accepted Answer · answered Oct 19 '16 at 13:10

Getting the new users is relatively straightforward - I'd add a calculated column which counts rows for that user in earlier months and if they don't exist then they are a new user:

=IF(CALCULATE(COUNTROWS(data), 
              FILTER(data, [Acc_ID] = EARLIER([Acc_ID]) 
                           && [Date_ID] < EARLIER([Date_ID]))) = BLANK(), 
    "new", 
    "existing")

Once this is in place you can simply write a measure for new_users:

=CALCULATE(COUNTROWS(data), data[customer_type] = "new")

Getting the cancelled users is a little harder because it means you have to be able to look backwards to the prior month - none of the time intelligence stuff in PowerPivot will work out of the box here as you don't have a true date column.

It's nearly always good practice to have a separate date table in your PowerPivot models and it is a good way to solve this problem - essentially the table should be 1 record per date with a unique key that can be used to create a relationship. Perhaps post back with a few more details.

Great, this seems to work. I do in fact have a linked date table in my model - sorry I forgot to mention in my initial question. — Yarwood, Oct 20 '16 at 05:02

score 0 · Answer 2 · answered Oct 20 '16 at 05:47

This is an alternative method to Jacobs which also works. It avoids creating a calculated column, but I actually find the calculated column useful to use as a flag against other measures.

=CALCULATE(
    DISTINCTCOUNT('Accounts'[Acc_ID]), 
    DATESBETWEEN(
        'Dates'[Date], 0, LASTDATE('Dates'[Date])
    )
) - CALCULATE(
    DISTINCTCOUNT('Accounts'[Acc_ID]), 
    DATESBETWEEN(
        'Dates'[Date], 0, FIRSTDATE('Dates'[Date]) - 1
    )
)

It basically uses the dates table to make a distinct count of all Acc_ID from the beginning of time until the first day of the period of time selected, and subtracts that from the distinct count of all Acc_ID from the beginning of time until the last day of the period of time selected. This is essentially the number of new distinct Acc_ID, although you can't work out which Acc_ID's these are using this method.

I could then calculate 'exited accounts' by taking the previous months total as 'existing accounts':

=CALCULATE(
    DISTINCTCOUNT('Accounts'[Acc_ID]), 
    DATEADD('Dates'[Date], -1, MONTH)
)

Then adding the 'new accounts', and subtracting the 'total accounts':

=DISTINCTCOUNT('Accounts'[Acc_ID])

Calculating the number of new ID numbers per month in powerpivot

2 Answers2