Calculate unsubscribe status

[twanqlik]

I want to check when a customer unsubscribes. 

Input table:

ID	MONTH	Subscribed status
1	2021-05	Yes
1	2021-04	No
1	2021-03	No
1	2021-02	Yes
1	2021-01	Yes
1	2020-12	No
1	2020-11	Yes
1	2020-10	Yes

Expected result:

ID	MONTH	Subscribed status
1	2021-05
1	2021-04
1	2021-03	Unsubscribed
1	2021-02
1	2021-01
1	2020-12	Unsubscribed
1	2020-11
1	2020-10

Any tips on how to accomplish this? 

The ordering of the months is of course important.

In the expected output table are only 2 Unsubscribed statuses, not 3.
The customer unsubscribed in March 2021, and was still unsubscribed in April 2021.
The action was performed in March, and that i want to visualize.  

[JuanGerardo · Accepted solution]

Hi @twanqlik, I think you can accomplish this with Previous() function:

NewTable:

Load

   ID,

   MONTH,

   If(ID = Previous(ID) And Previous([Suscribed status]) = 'Yes' And [Suscribed status] = 'No', 'Unsubscribed')  AS [Suscribed status]

Resident InputTable   // or your datasource

Order by ID, MONTH;

JG