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...
joseluis1969240's avatar
joseluis1969240
Frequent Visitor
2 years ago

Good evening, The approach you propose doesn’t work for me because it doesn’t take into account that, in my case, recurring sales are those that occur in the two years prior to the current one. Let me give you some examples: -Customers with sales in the previous year (01/04/2022-31/03/2023), THESE SALES ARE RECURRING
-Customers with sales in the year 01/04/2021-31/03/2022, THESE SALES ARE RECURRING
-Customers with sales in the year 01/04/2022-31/03/2023 and in the year 01/04/2021-31/03/2022, THESE SALES ARE RECURRING
-Customers with sales in the year 01/04/2023-31/03/2024 and no sales in the two previous years, THESE SALES ARE NEW (ACTUAL CUSTOMER)

Considering the previous examples, the approach I’m using involves the DATESBETWEEN function. Can you think of any other solution?

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.