identify lost customers using snapshotted data

[kzmlbyrk]

Hello,

There are two monthly snapshotted tables as shared below.

I need to create a new column in the customers table.
This column needs to show whether or not the customer has zero active units left in that snapshot while at least one active contract in the previous snapshot.

In the example below, the customer with ID 10 was left with zero active contracts on snapshot 202203 while having 1 active contract on 202202; and was flagged as a lost customer on 202203.

CustomerIDYearMonth column is the key between the two tables.

Thanks in advance!

Contracts table

Snapshot Year Month	Customer ID	CustomerIDYearMonth	Contract ID	Is Active Contract of This Month	Is Termination of This Month
202201	10	10202201	1	TRUE	FALSE
202202	10	10202202	1	TRUE	FALSE
202203	10	10202203	1	FALSE	TRUE
202204	10	10202204	1	FALSE	FALSE
202201	10	10202201	2	TRUE	FALSE
202202	10	10202202	2	FALSE	TRUE
202203	10	10202203	2	FALSE	FALSE
202204	10	10202204	2	FALSE	FALSE
202201	20	20202201	3	TRUE	FALSE
202202	20	20202202	3	TRUE	FALSE
202203	20	20202203	3	TRUE	FALSE
202204	20	20202204	3	TRUE	FALSE
202201	20	20202201	4	TRUE	FALSE
202202	20	20202202	4	TRUE	FALSE
202203	20	20202203	4	TRUE	FALSE
202204	20	20202204	4	TRUE	FALSE

Customers table 

Snapshot Year Month	Customer ID	CustomerIDYearMonth	Is Lost Customer of This Month (Desired Result)
202201	10	10202201	FALSE
202202	10	10202202	FALSE
202203	10	10202203	TRUE
202204	10	10202204	FALSE
202201	20	20202201	FALSE
202202	20	20202202	FALSE
202203	20	20202203	FALSE
202204	20	20202204	FALSE

[amitchandak]

@kzmlbyrk , refer if these approaches can help, you need join the date table on the snapshot date (you can create date using year month)

date = date( left([Snapshot Year Month],4) ,right([Snapshot Year Month],2),1)

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...