The data in the R03_Table fact table represents sample data for something like an e-commerce website where purchases are intermittent, as opposed to something like a monthly or yearly subscription service.
For the Lost Customers measure, the first two lines define the number threshold increments, __Threshold, as well as the threshold interval unit, __ThresholdUnit. Together, these define the period of time from a customer's last purchase, after which a customer is considered lost. This recipe supports any number for the value of __Threshold and the values DAYS, MONTHS, or YEARS for the value of __ThresholdUnit.
The next four lines simply collect information with regard to the current context within which the measure is performing its calculation and stores these in the __Month, __Year, __MonthStart, and __MonthEnd variables.
The next calculation is to create a table variable called __PreviousCustomers. This calculation uses FILTER to return the rows within the R03_Table fact table for all purchases made in all previous months. Note that the use of ALL or ALLEXCEPT is not required here because the matrix visualization uses the MonthName and Year columns from the R03_Dates table and the R03_Dates table is not related to R03_Table. The SUMMARIZE function is used to create a unique row for each customer and add the __LastPurchase column, whose value becomes the date of the last purchase date by that customer.
We continue to build upon our __PreviousCustomers table variable by using ADDCOLUMNS to add the __LostDate column and store this revised table in the __PreviousCustomers1 variable. While we could have combined these two steps into a single variable calculation, it is easier to understand, maintain, and modify the code with separate calculations for each step. The __LostDate column calculation is based upon the __ThresholdUnit value specified earlier. If DAYS is specified, we simply need to add the __Threshold variable to the LastPurchase column to arrive at the date upon which the customer is considered lost. If MONTHS is specified, we use the EOMONTH function to add the number of months specified by the __Threshold variable to our LastPurchase date. Finally, if YEARS is specified, we can construct LostDate by using the DATE function and adding the __Threshold variable to the YEAR of our LastPurchase date.
We can now compute a table of values for lost customers, __LostCustomers, by using the FILTER function to return only the rows in __PreviousCustomers1 whose LostDate has the same month, __Month, and year, __Year, as the current context provided by the matrix visualization. We use SELECTCOLUMNS to select only the Customers column and then use DISTINCT to only return unique values. We then simply need to return the number of rows in the __LostCustomers table variable using the COUNTROWS function.
For Recovered Customers, the first portion of the formula is nearly identical to Lost Customers. There is one difference in the calculation of the __PreviousCustomers variable, which is to include the date of a customer's first purchase in the __FirstPurchase column. Similarly, there is a slight difference in the calculation of the __PreviousCustomers1 variable, where we use FILTER to remove the rows for customers whose first purchase, __FirstPurchase, is in the current month under consideration. The calculation for the __LostCustomers variable is identical to the calculation in the Lost Customers measure.
Next, we need to identify the current customers within the month and year in question. We do this using FILTER, SELECTCOLUMNS, and DISTINCT to return a table that contains the unique values of customers who made purchases between the start of the month, __MonthStart, and the end of the month, _MonthEnd.
Once we know the values for all lost customers and current customers, we can compute recovered customers and store these in the __RecoveredCustomers variable. To do this, we use ADDCOLUMNS to add the __IsCurrent column to our __LostCustomers table variable. For the value of the __IsCurrent column, we check to see whether the customer specified in the __Customer column for the current row is in the __CurrentCustomers table. We do this using the IN function. The IN function returns true if the specified value is in a specified column of a table, and false if it is not.
Once we have identified rows in the __LostCustomers table variable that are also in the __CurrentCustomers table variable, we can simply use COUNTROWS to count the rows in __RecoveredCustomers where the __IsCurrent column is true and return this as the value for the measure.