Calculate difference value between two values in one column based on date and assignment

[Anonymous]

Hi all,

I want to create a calculated column named 'project' to come true the picture shown as below,

The 'project' value is 'NEW_VALUE' based on 'ESN' and 'FIELD' value equals 'A'.

And between two 'FIELD' values  euals 'A' based on 'CREATED_TS', the 'project' value euqals the older 'NEW_VALUE' value. 

Sample data as below:

CREATED_TS	FIELD	NEW_VALUE	ESN
2019/7/25 5:04	A	Reg_KI10001.01_0725	1
2019/7/25 5:20	A	Reg_KI10001.01_0724	1
2019/7/25 5:29	A	Reg_KI10001.01_0723	1
2019/8/6 4:27	A	Reg_KI10001.01_0805	1
2019/7/25 5:06	A	Reg_KK10001.00_0725	2
2019/8/22 6:41	A	Reg_KK10001.02_0822	2
2019/9/1 9:39	B	else1	2
2019/9/1 9:45	B	else2	2
2019/9/2 12:34	B	else5	2
2019/9/2 12:34	B	else4	2
2019/9/2 12:45	B	else3	2
2019/9/2 13:15	B	else6	2
2019/9/4 13:15	A	Reg_SJ10001.02_0823	2
2019/9/4 14:25	A	Reg_GI10001.02_0821	2

Please help me for this issue!

Thanks!

[Anonymous · Accepted solution]

Hi,

Check if this works:

project = 
VAR v_lag = CALCULATE( MAX( Data[NEW_VALUE] ), FILTER( Data, Data[CREATED_TS] <= EARLIER( Data[CREATED_TS] ) ) )
RETURN
IF( Data[FIELD] == "A", Data[NEW_VALUE], v_lag )

Here's what i got:

Thanks.

[Anonymous]

Hi,

I think "MAX( DATA[NEW_VALUE] )" does not works if "NEW_VALUE" like picture as below.