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!
Solved! Go to 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.
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.
Hi,
I think "MAX( DATA[NEW_VALUE] )" does not works if "NEW_VALUE" like picture as below.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
125 | |
78 | |
69 | |
55 | |
55 |
User | Count |
---|---|
200 | |
104 | |
85 | |
80 | |
77 |