cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors