dax measure/column to iterate for each occurance

[Anonymous]

Hello i could not find any previous post related to my issue, please help

I need to create a measure to show the date/time difference with reference to a column for each of its occurance. below screenshot shoudl help understand my scenario

find datediff of column1 and column2

Column2 condition:

Type = ExecutionTaskNodeWasEdited

Column2 = max(Column2) of the occurance

Column1 Condition:
Type = ExecutionEnteredTaskNode // there could be multiple ExecutionEnteredTaskNode with same or different timestamp

Column1 = Min(Column1) // very first occurance after "ExecutionTaskNodeWasEdited"

DateTime	Type	Column1	Column2
8/22/2022 8:15	ExecutionEnteredTaskNode	2022-08-22T08:15:57
8/22/2022 8:16	ExecutionTaskNodeWasEdited		2022-08-22T08:16:04
8/22/2022 8:16	ExecutionExitedTaskNode
8/22/2022 8:43	ExecutionEnteredTaskNode	2022-08-22T08:15:57
8/22/2022 8:43	ExecutionExitedTaskNode	2022-08-22T08:15:57
8/22/2022 10:54	ExecutionEnteredTaskNode	2022-08-22T08:15:57
8/22/2022 10:54	ExecutionTaskNodeWasEdited		2022-08-22T10:54:59
8/22/2022 10:55	ExecutionExitedTaskNode	2022-08-22T11:08:39
8/22/2022 11:08	ExecutionEnteredTaskNode	2022-08-22T11:08:39
8/22/2022 11:08	ExecutionExitedTaskNode	2022-08-22T11:08:39
8/22/2022 11:09	ExecutionEnteredTaskNode	2022-08-22T11:08:39
8/22/2022 11:09	ExecutionTaskNodeWasEdited		2022-08-22T11:09:14
8/22/2022 11:09	ExecutionExitedTaskNode

[ryan_mayu · Accepted solution]

@Anonymous 

you can try to create an index column and use DAX to create another column

Column = 
VAR _last=MAXX(FILTER('Table','Table'[Index]<EARLIER('Table'[Index])&&'Table'[Type]="ExecutionTaskNodeWasEdited"),'Table'[Index])
var _last2=minx(FILTER('Table','Table'[Index]>_last&& 'Table'[Type]="ExecutionEnteredTaskNode"),'Table'[Column1])
return if(ISBLANK('Table'[Column2]),blank(),DATEDIFF(_last2,'Table'[Column2],SECOND))