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 
Please check if the following would work for you?

Sales Returning Customers:=
Var YearMax = MAX(Calendario[Fiscal_Year_1])
Var Actual_Customer=
	CALCULATETABLE(
		VALUES(Ventas[Customer Account]);
		Calendario[Fiscal_Year_1] = YearMax
	)
Var Past_Customers=
	CALCULATETABLE(
		VALUES(Ventas[Customer Account]);
		Calendario[Fiscal_Year_1] = YearMax-1
		
	)
Var Cross=INTERSECT(Past_Customers;Actual_Customer)
Return
	CALCULATE(
		[Sales];
		Cross
	)
  • 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.