Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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. 

 

capture.PNG

 

Sample data as below:

 

CREATED_TSFIELDNEW_VALUEESN
2019/7/25 5:04AReg_KI10001.01_07251
2019/7/25 5:20AReg_KI10001.01_07241
2019/7/25 5:29AReg_KI10001.01_07231
2019/8/6 4:27AReg_KI10001.01_08051
2019/7/25 5:06AReg_KK10001.00_07252
2019/8/22 6:41AReg_KK10001.02_08222
2019/9/1 9:39Belse12
2019/9/1 9:45Belse22
2019/9/2 12:34Belse52
2019/9/2 12:34Belse42
2019/9/2 12:45Belse32
2019/9/2 13:15Belse62
2019/9/4 13:15AReg_SJ10001.02_08232
2019/9/4 14:25AReg_GI10001.02_08212


Please help me for this issue!

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:

result.PNG

 

Thanks.

View solution in original post

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:

result.PNG

 

Thanks.

Anonymous
Not applicable

Hi,

 

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

 

capture.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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