Find the previous value of a specific column

Good afternoon,
I have a simple data set with these column


I’m trying to create a calculated field to find the previous “FatherAlarm” DoEnd data. My goal is this

I’m trying with the lag function, but I’m not able to have the previous DoEnd of a specific “AlarmCode” condition. How can I do it?

Best regards

I think you need to partition by FatherAlarmCode.

     ,[ DoEnd]  
     ,[ {FatherAlarmCode} ] 
1 Like

Already tried, without any luck. This is the result of the lag function


I tryed Lag and Lead, with ASC or DESC, but I never get what I need

@egobbo try defining a transform rule for Nulls in FatherAlarmCode to get the desired result. Your sample code would look like this…

lag({DoEnd}, [ DoEnd] ,1,[ nullif({FatherAlarmCode},AlarmCode),{FatherAlarmCode} ] )

1 Like

Great suggestion!

To get what I need, I created one more additional calculated field

DoStartPart → formatDate({DoStart}, ‘dd/MM/yyyy’)

then I changed my PrevFatherDoEnd in this way

firstValue(DoEnd, [ DoStart ASC ],[ifelse(isNull(FatherAlarmCode),AlarmCode,FatherAlarmCode),DoStartPart ])

in this way the result is this

that is what I was looking for!

1 Like