Customer Activation calculation

[KonradP]

Dear Community

I need to calculate the activation rate for customers in a given period of time. The measure is defined as:

Тhe numerator: Count of first transactions in a given period of time made only by the customers subscribed by the same period of time

The Denominator: Count of customers in a given period of time

For example,
20 customers subscribed in December 10 of them made the first transaction.
30 customers subscribed in January, 10 of them made the first transaction as well as 5 customers who subscribed in December.

So if we want to filter January we have:
30 customers subscribed,
15 first transactions but only 10 of them made by customers subscribed in January.
So our AR according to the definition is 10/30 = 33%

Now both customer and transaction tables have different dates obviously. So I covered this scenario using double date slicer - one for a customer sub date and one for transaction date. However, this seems very clunky and inconvenient. I figure there must be some way to use one slicer. I tried sth like this to calculate the numerator but it didn't work:

I create CommonDataDim table to connect with SubscriptionDate (active rel) and with TransactionDate (unactive rel)
Then I create this measure for the Numerator. I figure I just use CALCULATE and 2 date fields as a filter but it did not work:

Numerator = CALCULATE(COUNT(Transactions[first_transation]);
//subscription date relation
CommonDataDim[FullDateAltKey];
//making transaction date relation active
USERELATIONSHIP(CommonDataDimFullDateAltKey];'Transactions'[Transation_Date]))

To put it simply
I 'd like to have my first transactions count filtered through the one slicer a) by transaction date and then b) by subscription date of customer that made this transaction. And then divide this number by count of subscribed customers in this period. I hope it's clear enough.

I would appreciate any help

EDIT - i would attached some screen to visualize the problem

So this is how the model looks like:

We have transaction Facts, Customer Dimensions, and Date tables
Both Customer and Transactions Table have their own data tables and also one common (where CustomerSubDate is the active relation and Transacion Date is unactive).
Transactions and Customers Are related by Customer ID

This is how the table looks like for Feb 2019 The fields are:

1. Date hierarchy from SubDate
2. Total lead # is the measure to count Subscribed Customers by their ID
3. First Transfer Y/N is the sum of Transactions flagged as a first (1 is first, 0 is not first)
4. Activation % is just a division of First Transfers and Lead #. Obviously it only works because of the 4 slicers.
The table also has its own filter ( First Transfer >0) as You can see on the screen (it could be a fifth slicer though)

If I would do it in excel, where I have one flatted table of transactions I would do the following fitlering:

1 Transaction date = <given month>
2 First Transfer Y/N = 1
3 Customer Subscription Date = <given month>(customers ID are next to the transfer ID, so every transaction has it's own portion of customer date)

And that's it

I feel like the answear is simple but I'm not just experienced enough to see it 🙂

[v-eachen-msft]

Hi @KonradP ,

You can try to create a new column with RANKX() function based on date and customer columns. Then calculate the count where rank =1.

You could share your sample data and excepted result to us if you don't have any Confidential Information.

[KonradP]

Hi @v-eachen-msft 

I provided some details about the model and sample data table. Would mind checking out?

[JarroVGIT]

I think I understand what you are trying to achieve, but I could really use some sample data to test my idea. 

Roughly: I am thinking of creating a measure that does it all in once by:
1. _curContext = Creating a variable that holds the current selected timeframe of subscribers (basically, the current context of Subscriber table)
2. _addedColumn = Create a second variable by adding a column in the first table variable, that looks if the first transaction of current row subscriber is within MAX(subscriber[date]) and MIN(subscriber[date]) and returns true if that is the case, and false when that is not the case
3. return the DIVIDE(COUNTROWS(FILTER(_addedColumn, [addedColumn] = TRUE)), COUNTROWS(_curContext))

When adding this into a context filtered by month (e.g. a matrix or a table), this would give you your activation 🙂 But without the table structure and column names I can't produce any DAX for you now, but maybe this is help enough already? Let me know! 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[KonradP]

Hi @JarroVGIT ,

Thank You, I just edited the post and put some screens explaining the model. Could you please check it out?