cancel
Showing results for 
Search instead for 
Did you mean: 
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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors