identify lost customers using snapshotted data

Question

Hello,&nbsp;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.&nbsp;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.&nbsp;CustomerIDYearMonth column is the key between the two tables.&nbsp;Thanks in advance!&nbsp;Contracts tableSnapshot Year MonthCustomer IDCustomerIDYearMonthContract IDIs Active Contract of This MonthIs Termination of This Month20220110102022011TRUEFALSE20220210102022021TRUEFALSE20220310102022031FALSETRUE20220410102022041FALSEFALSE20220110102022012TRUEFALSE20220210102022022FALSETRUE20220310102022032FALSEFALSE20220410102022042FALSEFALSE20220120202022013TRUEFALSE20220220202022023TRUEFALSE20220320202022033TRUEFALSE20220420202022043TRUEFALSE20220120202022014TRUEFALSE20220220202022024TRUEFALSE20220320202022034TRUEFALSE20220420202022044TRUEFALSE&nbsp;Customers table&nbsp;Snapshot Year MonthCustomer IDCustomerIDYearMonthIs Lost Customer of This Month (Desired Result)2022011010202201FALSE2022021010202202FALSE2022031010202203TRUE2022041010202204FALSE2022012020202201FALSE2022022020202202FALSE2022032020202203FALSE2022042020202204FALSE

amitchandak · Answer

kzmlbyrk , refer if these approaches can help, you need join the date table on the snapshot date (you can create date using year month) 
&nbsp;
date = date( left([Snapshot Year Month],4) ,right([Snapshot Year Month],2),1)
&nbsp;
Customer Retention Part 1:https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

Forum Discussion

identify lost customers using snapshotted data

1 Reply

Related content

Identifying New Customer Sales

Identifying duplicates

Power BI embedded in PowerPoint – Live /Snapshot/Public Snapshot buttons not working (recent regr?)

Rolling Date Identifiers

Warehouse Snapshots: Dropping tables deletes the tables from Snapshots

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