Forum Discussion

Marvhall's avatar
Marvhall
Helper I
4 years ago
Solved

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 u...
Need Help
amitchandak's avatar
amitchandak
Super User
4 years ago

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/1377458

 

 

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's avatar
Marvhall
Helper I
4 years ago

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