Power BI and Azure Data Platform

Customer Churn DAX pattern

• 2015 New and returning customers explained on DAX patterns site.

  • Complete pattern example
    This includes both absolute and relative measures.

    
    

  • Improved using VAR and a set function

      MEASURE Customer[NewCustomersSet] =
        VAR CurrentCustomers = VALUES ( Sales[CustomerKey] )
        VAR OldCustomers = 
            FILTER (
                CurrentCustomers,
                CALCULATE (
                    MIN ( Sales[OrderDate] ), 
                    ALL ( 'Date' )
                ) < MIN ( 'Date'[FullDate] )
            )
        RETURN COUNTROWS ( EXCEPT ( CurrentCustomers, OldCustomers ) )
    

  • Sales by Customer type example
    Here the list of customers is a filter argument in the relevant CALCULATE expression.

    
    

• 2016-05 Faster New and Returning Customers explained on the SQLBI site.
  This version of the measure is much faster than the one published in 2015.

  MEASURE Sales[NewCustomers] =
    COUNTROWS (
        FILTER (
            CALCULATETABLE (
                ADDCOLUMNS (
                    VALUES ( Customer[CustomerKey] ),
                    "DateOfFirstBuy", CALCULATE ( MIN ( Sales[OrderDate] ) )
                ),
                ALL ( 'Date' )
            ),
            CONTAINS (
                VALUES ( 'Date'[FullDate] ), 
                'Date'[FullDate], 
                [DateOfFirstBuy] 
            )
        )
    )
  

  
  

• 2017 Customer Attribution Analysis explained by Sam McKay.

• 2020-02 More measures for Customer Retention explained by Reza Rad

Return to:

Top

Back to overview of DAX patterns

Basket Analysis DAX pattern

Home