Need help calculating for a matrix

[Marvhall]

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!

[amitchandak · Accepted solution]

@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/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Marvhall]

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]))

RETURN

IF( 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")

             )

       )

)