Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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 betwee...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

     

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