Clients change in status

[mihaita_baro]

Hi guys,

I have the below table and each Client have multiple review sessions and all i am looking for is to calculate how many clients change from Low Intensity to High Intensity and i am struggle to find it how. Any hint or tips is good.

ClientId	revreviewID	revreviewDate	revsupportcategory
667774	2606977	25/02/2020	Low Intensity: 1st Step
667774	2617856	13/03/2020	High Intensity: 1st Step
667774	2623659	25/03/2020	Low Intensity: 1st Step
667774	2633217	08/04/2020	High Intensity: 1st Step
667774	2641795	22/04/2020
667774	2646529	29/04/2020	Low Intensity: Stepped Down
667774	2659722	20/05/2020	Low Intensity: 1st Step
668446	2490919	20/08/2019	High Intensity: 1st Step
668446	2490920	10/09/2019	Low Intensity: 1st Step
668446	2509055	24/09/2019	High Intensity: 1st Step
668446	2509101	08/10/2019
668446	2528384	05/11/2019	High Intensity: 1st Step
668446	2546209	22/11/2019	High Intensity: 1st Step
668446	2546210	06/12/2019	High Intensity: 1st Step
668446	2577662	07/01/2020
669941	2651390	06/05/2020	Low Intensity: 1st Step
669941	2654482	12/05/2020	Low Intensity: 1st Step
669941	2683689	06/07/2020	High Intensity: 1st Step

[amitchandak · Accepted solution]

@mihaita_baro , Try if a new column like this can help

New column =
var _cli =[ClientId]
var _dt = [revreviewDate]
Var _max = maxx(filter(Table, [ClientId] =_cli && [revreviewDate] <_dt ), [revreviewDate])
var _st = maxx(filter(Table, [ClientId] =_cli && [revreviewDate] =_max ), [revsupportcategory])
return
if(_st ="Low Intensity" && [revsupportcategory] = "High Intensity" , 1, blank())