Forum Discussion
Customer Activation calculation
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.
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%
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:
//subscription date relation
CommonDataDim[FullDateAltKey];
//making transaction date relation active
USERELATIONSHIP(CommonDataDimFullDateAltKey];'Transactions'[Transation_Date]))
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.
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
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)
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)
4 Replies
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! π
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.
I provided some details about the model and sample data table. Would mind checking out?
