cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors