Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Customer retained

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 

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    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

1 Reply

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    5 years ago

    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