Recurring Customers

[joseluis1969240]

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.

---

[Fowmy]

@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]

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]

@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]

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]

@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]

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]

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

[joseluis1969240]

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]

@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

[joseluis1969240]

Good morning:
The measure you mentioned works perfectly, but I need it to work dynamically. Let me explain: if I generate a pivot table with the 'customer name' field and the 'lost customers' measure, the pivot table's result is correct. HOWEVER, if I apply a filter to the pivot table by 'fiscal year,' the table appears without data. Could you please help me modify the measures you've taught me to make them work when I apply filters by year in pivot tables? These are the last two measures you provided and that I need to function when I apply filters to the pivot tables in which I use them:

Thank you very much again