Forum Discussion
Need help calculating for a matrix
Good afternoon,
I am looking to update a calculated column with different values (New user based on ID, Existing user (based on ID)), number users ended service (date column), and the number of users re-enrolled (date column).
Currently I have this calculated column formula:
Customer Profile =
var CustomerID = 'table'[ID]
var MonthIndex = 'table'[Month Index]
var PreviousCount = Calculate(Countrows('table'), FILTER(ALL('table'), 'table'[ID]' = CustomerID && 'table[month Index] < Monthndex ) )
Return
IF( PreviousCount > 0, "Previous Customer,", "New Customer")
I can't figure out how to add the other measures to this profile. If I put them in separately, they don't show up as rows or they show up as a hierarchy to New and Previous customers.
Thanks in Advance!
Marvhall , refer if my logic os using isblank with previous period can help
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
2 Replies
Marvhall , refer if my logic os using isblank with previous period can help
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.Thank you, I took some of your logic and made it into this:
I created an index column because the customer ID is repetative monthly:
Index = RANKX (FILTER ( 'Table 1', EARLIER('Table 1'[Customer_ID]) = 'Table 1'[Customer_ID]), 'CTable 1'[YearMonth], , ASC)Then based the profile off the index numbers.
_Customer Profile =varCustomerID = 'Table 1'[Customer_ID]var MonthIndex = 'Table 1'[_Month Index]var previousCount = CALCULATE(COUNTROWS('Table 1'), FILTER(ALL('Customer_ID'),'Table 1'[Customer_ID] = CustomerID && 'Table 1'[_Month Index]))RETURNIF( NOT ISBLANK('Table 1'[Customer_End_Date]), "Customer Quit",IF( 'table 1'[Index] = 1, "New Customer",IF('Table 1'[Index] =2, "Previous Customer", "Same Customer"))))
