Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

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

View solution in original post

1 REPLY 1
V-lianl-msft
Community Support
Community Support

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.