cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## 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 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

Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

2 REPLIES 2
Anonymous
Not applicable

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
Not applicable

Hi,

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors