Churned MRR calculation

[Hailey_Hung]

Hey guys,

I'm new to Power BI. I'm writing MRR analysis. Completed count of churned customer, new customer and retained customer using following measures:

CustCountChurned = - sumx(VALUES(Cust_lookup[Cust#]), if(not(ISBLANK('GL'[RevLastMTD])) && ISBLANK('GL'[RevMTD]) ,1,BLANK()))


CustCountNew = sumx(VALUES(Cust_lookup[Cust#]), if(ISBLANK('GL'[RevLastMTD]) && not(ISBLANK('GL'[RevMTD])) ,1,BLANK()))


CustCountRetained = sumx(values(Cust_lookup[Cust#]),if(not(ISBLANK('GL'[RevLastMTD])) && not(ISBLANK('GL'[RevMTD])) , 1,BLANK()))

Datasets look like this:

Need your help on calculate the revenue of churned customer, new customer and retained customer with following definitions:

• Rev of Customter churned = prior month revenue from customers churned in current month
• Rev of new Customer = current month revenue from new customer in current month
• Rev of Customers retained = current month revenue from retained customer in current month

I would very appreciation your help 🙂
p.s. I have read post on similar topics but can't find solutions. I tried to use the following measure to calculate revenue of customer churned but failed.  

RevChurned = 
VAR ChurnedCustomers =
    CALCULATETABLE(
        VALUES(Cust_lookup[Cust#]),        
        ISBLANK('GL'[RevMTD]) && NOT(ISBLANK('GL'[RevLastMTD]))
        )
RETURN
CALCULATE(
    SUM(Revenue[GLAmtAdj.]),
    Cust_lookup[Cust#] IN ChurnedCustomers
)

[v-tianyich-msft]

Hi @Hailey_Hung ,

Can you provide your sample data and expected results?

Best regards,
Community Support Team_ Scott Chang