Hi there,
I've been grappling for quite some time now the repeat customer scenarion. I had a colegues do a sql script giving an output for repeat customers rethat bought in the last 12 months (even if they did not buy in the current month).
However no I need to do it in Dax after all as the client wants to filter and drill on CustomerEmail for repeated customer only.
1. Does anyone have the same solution in DAX
2. In the case yes, can the SQL to DAX be amended to to rather look 12 months back from the selected date (YYYY-MM)
The SQL script is as follows:
/*
Repeat Customer Metric Data:
This metric collects information about repeat customers that bought in the last 12 months (even if they did not buy in the current month)
*/
DECLARE @tempdate date = CONVERT(date,GETDATE())
DECLARE @CurrentMonthStart date = DATEADD(DAY,1-DAY(@tempdate),@tempdate)
DECLARE @OrderHeader TABLE (
Customer_Email varchar(max),
OrderInvoiceNo varchar(max),
CompletedDate date,
Total decimal(19,2)
)
INSERT INTO @OrderHeader (Customer_Email, OrderInvoiceNo, CompletedDate, Total)
SELECT DISTINCT Customer_email, OrderNumber, CONVERT(date,LEFT(CompletedDate, CHARINDEX(' ', CompletedDate)),103) AS CompletedDate, CONVERT(decimal(19,7), [OrderQuantity])*CONVERT(decimal(19,7), [DiscountedUnitPrice]) AS Total
FROM [TableA]
WHERE Customer_email IS NOT NULL AND Customer_email <> '' AND CompletedDate IS NOT NULL AND
CONVERT(date,LEFT(CompletedDate, CHARINDEX(' ', CompletedDate)),103) > DATEADD(year,-2,@CurrentMonthStart) AND
CONVERT(decimal(19,7), [OrderQuantity])*CONVERT(decimal(19,7), [DiscountedUnitPrice]) IS NOT NULL
DECLARE @counter int = 0
DECLARE @Max int = 11
DECLARE @DateVariable date
DECLARE @Output TABLE (
Customer_Email varchar(max),
MonthDate date,
TotalNetSales decimal(19,2)
)
WHILE (@counter <= @Max)
BEGIN
SET @DateVariable = DATEADD(MONTH,-@counter, @CurrentMonthStart)
INSERT INTO @Output(Customer_Email, MonthDate, TotalNetSales)
SELECT RC.Customer_Email, RC.StartDate AS MonthDate, TotalNetSales
FROM (SELECT Customer_Email , DATEADD(MONTH,-1,@DateVariable) AS StartDate, DATEADD(DAY,-1,@DateVariable) AS EndDate, COUNT(CompletedDate) AS NoOfOrders, SUM(Total) AS TotalNetSales
FROM @OrderHeader
WHERE CompletedDate BETWEEN DATEADD(YEAR,-1,@DateVariable) AND DATEADD(DAY,-1,@DateVariable)
GROUP BY Customer_Email
HAVING COUNT(CompletedDate) > 1 ) AS RC
SET @counter = @counter+1
END
SELECT * FROM @Output
Many thanks
