Count Customers who purchase a blue product the day after they purchased a Red product

Count Customers who purchase a blue product the day after they purchased a Red productFollow
Leonard Levine
Leonard LevineMar 28, 2023 03:49 PM
I have a table that has Service Date, Service and Client_Name. Serv ices include CCS1, CCS2, CCS3, Eval1, …
1. Count Customers who purchase a blue product the day after they purchased a Red product

Silver Contributor
Leonard Levine
Posted Mar 28, 2023 03:49 PM
I have a table that has Service Date, Service and Client_Name.
Services include CCS1, CCS2, CCS3, Eval1, Eval2 and Eval
Each line/record is a Client Name, Service Date and Service
Clients can have any the same or different services on multiple days (e.g. multiple records)
Simply, I would like to know how many clients had any of the Eval services and one of the CCS services the next day.
We have lots of examples like this where we need to track specific followup for specific things by member or patient. Sometimes its the next day, sometimes within several days.
Thanks,
Len

2. RE: Count Customers who purchase a blue product the day after they purchased a Red product

Bronze Contributor
James Watts
Posted Mar 29, 2023 03:58 AM
I’m sure there are some clever DAX ways of doing it, but it might be simplest in M Query to split the data into two tables, one for Eval and one for CCS services, then add a day to the Eval entries, then merge the two together with an inner join to find the matches.

——————————
James Watts
Business Intelligence Analyst
——————————

3. RE: Count Customers who purchase a blue product the day after they purchased a Red product

Silver Contributor
Leonard Levine
Posted Mar 29, 2023 06:52 AM
That is a simple clever thought. I will try. Thanks!

4. RE: Count Customers who purchase a blue product the day after they purchased a Red product

Lan Huynh
Posted Mar 29, 2023 11:52 PM
Edited by Lan Huynh Mar 29, 2023 11:56 PM
This would work as a single DAX measure:
Measure =
CALCULATE(
DISTINCTCOUNT(SampleData[Client Name])
, FILTER(
ADDCOLUMNS(
SampleData
, “CCS After Eval”
, COUNTROWS(
FILTER(
SampleData
, CONTAINSSTRING( SampleData[Service], “Eval” )
&& SampleData[Client Name] = EARLIER( SampleData[Client Name] )
&& SampleData[Service Date] =
IF(
CONTAINSSTRING( EARLIER( SampleData[Service] ), “CCS” )
, EARLIER( SampleData[Service Date] ) – 1
)
)
) > 0
)
, [CCS After Eval]
)
)
If you are fine with adding a column to the table in data view, just turn the ADDCOLUMNS clause into a column. It would make the measure formula neater.

——————————
Lan Huynh
Data Visualisation Developer
——————————

5. RE: Count Customers who purchase a blue product the day after they purchased a Red product

Silver Contributor
Leonard Levine
Posted Mar 30, 2023 06:47 AM
Really appreciate and plan to try today!