Customer retained

[Anonymous]

Hello ,

Can you please let me know calcualting below dax function :

Account Retention : # of active customers that have ramaning active every month ,over the last 12 months .
New coustomer : # of custmers that have not purchase ovet the prevoius 90 days

active account : # of customers that purchased on the last month 

Datasource: using one fact table with order date and Date dim table 

[V-lianl-msft · Accepted solution]

Try to create measures like below:

Account Retention = 
var active_month= 
CALCULATE(
    COUNT(Sheet3[Customer]),
    FILTER(
        ALLEXCEPT(Sheet3,Sheet3[date].[Year],Sheet3[date].[Month],Sheet3[Customer]),
        Sheet3[date]>=EDATE(TODAY(),-12)
    )
)
return CALCULATE(
        DISTINCTCOUNT(Sheet3[Customer]),FILTER(Sheet3,active_month=12))

New Customer Date = 
CALCULATE (
    MIN ( Sheet3[date]),
    ALLEXCEPT (
        Sheet3,
        Sheet3[Customer]        
    )
)

New Customers = 
VAR CustomersWithNewDate =
    CALCULATETABLE (                       
        ADDCOLUMNS (                       
            VALUES ( Sheet3[Customer] ), 
            "NewCustomerDate",[New Customer Date]
        ),
        ALLEXCEPT ( Sheet3, Sheet3[Customer] )
    )
VAR NewCustomers =
    FILTER (                              
        CustomersWithNewDate,             
        [NewCustomerDate]                
            IN CALENDAR(TODAY()-180,TODAY())
    )
VAR Result =                              
    COUNTROWS ( NewCustomers )            
RETURN
    Result

active account = CALCULATE(DISTINCTCOUNT(Sheet3[Customer]),FILTER(Sheet3,Sheet3[date]>=EDATE(TODAY(),-1)))

Sample .pbix