I'm trying to identify if a customer has renewed their license, then count the number of renewals by year.

[mcorser]

I have a set of customer data, with fields CustomerID, PurchaseYear, and ProductID. There may be multiple data points for each CustomerID in a PurchaseYear, depending on ProductID. In other words, the same person can purchase more than one type of license in a given year, over multiple years.

I'd like to have a KPI describing how many customers have maintained "engagement" by purchasing licenses in subsequent years.

CustomerID	PurchaseYear	ProductID
1	2016	B
1	2017	B
1	2020	A
1	2022	B
2	2019	A
2	2020	A
2	2020	B
2	2021	A
3	2017	A
3	2019	A
3	2020	A
3	2021	A

For example, if the selected year is 2020, the total customer renewals would be 2, despite each customer making a purchase in 2020. Only two customers maintained engagement from 2019.

Any suggestions?

[edwin]

im sure there would be a very smart way of phrasing an expression with a exists function where prior purcase for the same product id /customer combination exists, but i would propose a simpler and faster way.  move some of the logic to the script and it may optimize your dashboard.  since the fact that the customer purchased the same product the previous year will never change, you can flag that in the DM:

Purchases:
load *, PurchaseYear-1 as PriorPurchaseYear inline [
CustomerID,PurchaseYear,ProductID
1,2016,B
1,2017,B
1,2020,A
1,2022,B
2,2019,A
2,2020,A
2,2020,B
2,2021,A
3,2017,A
3,2019,A
3,2020,A
3,2021,A
];

left join (Purchases)
load CustomerID, ProductID, PurchaseYear as PriorPurchaseYear , 1 as PurchasedPreviousYear
Resident Purchases;

then in your KPI just add the modifier PurchasedPreviousYear={1}, here is the demo:

[edwin]

f course this assumes that the Purchasedate field will always have one selection