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 say, fiscal year 2024 (01/04/2023-30/04/2024) refer to the amount of sales to customers in fiscal year 2024 when I've sold to these customers in fiscal year 2023 or fiscal year 2022. In other words, if the customer has sales in either or both of the previous two fiscal years, the customer is considered recurrent.

To achieve this, I've developed the following measure:

 

"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]);DATESBETWEEN(Calendario[Date];DATE(2021;04;01);DATE(2023;03;31)))
Var Cross=INTERSECT(Past_Customers;Actual_Customer)
Return
CALCULATE([Sales];Cross)"

 

The above measure works well, BUT I would like to make the function "DATESBETWEEN(Calendar[Date], DATE(2021, 04, 01), DATE(2023, 03, 31))" dynamic (work in a filtered context). Is this possible?

 

I'm looking forward to your response. Thank you very much in advance.

 

 
 
 
 

10 Replies

  • 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 )