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 

I built this measure using the Contoso data model and it gave me the expected results, please modify as per your model and try:

Sales Returning 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 ActualvsYr1 =
    INTERSECT ( Actual_Customer, Actual_Customer_1Yr )
VAR ActualvsYr2 =
    INTERSECT ( Actual_Customer, Actual_Customer_2Yr )
VAR RecurringCustomers =
    DISTINCT ( UNION ( ActualvsYr1, ActualvsYr2 ) )
RETURN
    CALCULATE ( [Sales Amount], RecurringCustomers )
joseluis1969240's avatar
joseluis1969240
Frequent Visitor
2 years ago

Good afternoon:
Thank you very much, the measure works very well. I have tried to use this measure by modifying it to create two new measures: NEW CUSTOMERS AND LOST CUSTOMERS. I am not able to obtain the desired results, could you please continue helping me?

  • NEW CUSTOMERS: CUSTOMERS WITH SALES IN THE CURRENT FISCAL YEAR AND NO SALES IN THE PREVIOUS TWO FISCAL YEARS
  • LOST CUSTOMERS: CUSTOMERS WITH NO SALES IN THE LAST THREE FISCAL YEARS
    If it's not too much to ask, would you be so kind to detail the code for both measures? Do I need to open a new post to ask you these new questions?
    Thank you in advance for your help.
  • 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 )