Forum Discussion

joseluis1969240's avatar
joseluis1969240
Frequent Visitor
2 years ago

Recurring Customers

Good morning, I'm trying to define a measure in DAX that provides me with the amount of recurring sales per customer for each fiscal year I choose. In my case, recurring sales for, let's...
Fowmy's avatar
Fowmy
Super User
2 years ago

joseluis1969240 

Measure for new customers:

New Customers =
VAR YearMax =
    MAX ( 'Date'[Fiscal Year] )
VAR Actual_Customer =
    CALCULATETABLE (
        VALUES ( 'Sales'[CustomerKey] ),
        'Date'[Fiscal Year] = YearMax
    )
VAR Actual_Customer_1Yr =
    CALCULATETABLE (
        VALUES ( 'Sales'[CustomerKey] ),
        'Date'[Fiscal Year] = YearMax - 1
    )
VAR Actual_Customer_2Yr =
    CALCULATETABLE (
        VALUES ( 'Sales'[CustomerKey] ),
        'Date'[Fiscal Year] = YearMax - 2
    )
VAR Year1_2 =
    DISTINCT ( UNION ( Actual_Customer_1Yr, Actual_Customer_2Yr ) )
VAR Result =
    EXCEPT ( Actual_Customer, Year1_2 )
RETURN
    CALCULATE ( [Sales Amount], Result )


Measure for lost customers:

Lost Customers =
VAR YearMax =
    MAX ( 'Date'[Fiscal Year] )
VAR Result =
    SUMX (
        VALUES ( Customer[CustomerKey] ),
        VAR __Sales3yrs =
            CALCULATE (
                [Sales Amount],
                'Date'[Fiscal Year]
                    IN {
                        YearMax,
                        YearMax - 1,
                        YearMax - 2
                    }
            )
        VAR __Salesbefore3yrs =
            CALCULATE ( [Sales Amount], 'Date'[Fiscal Year] < YearMax - 2 )
        RETURN
            IF ( ISBLANK ( __Sales3yrs ), __Salesbefore3yrs )
    )
RETURN
    Result



joseluis1969240's avatar
joseluis1969240
Frequent Visitor
2 years ago

Good afternoon:

Thank you again for your help. The measure that calculates sales to new customers works perfectly; however, I'm having trouble implementing the measure that calculates sales from lost customers. Power Pivot is giving me the following error:

 

As you can see, I've copied the measure you provided (adjusting the field names accordingly). Could you assist me again?
Thanks in advance.

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    You missed a bracket:
    IF ( ISBLANK ( __Sales3yrs ), __Salesbefore3yrs )

    • joseluis1969240's avatar
      joseluis1969240
      Frequent Visitor
      2 years ago

      Good afternoon again:

      I've just corrected the error you pointed out, but I'm still facing an issue: the measure doesn't support filters, meaning if I create a pivot table with the 'customer' field and the "lost customers" measure, and try to filter a specific year (for example: 2024), the pivot table doesn't display any data... Could this measure be modified to show results when filters are applied? Lastly, I would like to ask if it's possible to dynamically determine the number of lost customers as well as the amount of their sales.

      Thank you again for your patience. Best regards,

      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        joseluis1969240 

        Please try this measure for lost customers, it shows the count. I am not sure if the amount makes sense here.

        Lost Customers = 
Var YearMax = MAX('Date'[Fiscal Year])
var Result = 
    SUMX(
        VALUES( Customer[CustomerKey] ),
        VAR __Sales3yrs = 
            CALCULATE(
               COUNTROWS( VALUES(Sales[CustomerKey] ) ),
                'Date'[Fiscal Year] IN { YearMax , YearMax-1  ,  YearMax-2    }        
            )
        VAR __Salesbefore3yrs = 
           CALCULATE(
               COUNTROWS( VALUES(Sales[CustomerKey] ) ),
                'Date'[Fiscal Year] <  (YearMax-2)
            )
        
        RETURN 
            IF( __Sales3yrs <> 1 && __Salesbefore3yrs =1 ,  1 )
    )
RETURN
    Result