Earlier TaskCreationDate

[ElliotK]

Hello,

Trying to capture the TaskCreationDate from row 5 and display it on row 6.  In essence, whenever there is a TaskPositionID 4, followed by and TaskPositionID 3, I wish to see the TaskCreationDate on the completed line. The TaskSTatusID must remain the same, as will the eFileID. The TaskID is unique. I have tried to do this using a new column but PBI does not like the syntax. 

TaskID	TaskGroupID	TaskStatusID	TaskStatus	StartDate	EndDate	TaskPositionID	TaskPosition	eFileID	TaskCreationDate
1746	3	1	Processing	28/07/2022	NULL	2	In Progress	38	13/07/2022 07:00
1820	3	1	Processing	28/07/2022	NULL	2	In Progress	38	13/07/2022 08:00
1813	3	1	Processing	28/07/2022	NULL	2	In Progress	38	13/07/2022 09:00
2757	3	1	Processing	28/07/2022	NULL	4	Queried Technician	38	13/07/2022 10:01
2032	6	1	Processing	28/07/2022	29/07/2022	3	Completed	38	13/07/2022 12:00

I have tried using the below syntax:

EARLY = CALCULATE(MAX('vw_Tasks_all'[TaskCreationDate]),
FILTER(vw_Tasks_all,'vw_Tasks_all'[eFileID]=EARLIER('vw_tasks_all'[eFileID])
&&
'vw_Tasks_All'[TaskCreationDate]>EARLIER('vw_tasks_all'[TaskCreationDate])
&&
vw_Tasks_all,'vw_Tasks_all'[TaskStatusID]=EARLIER('vw_tasks_all'[TaskStatusID]
&&
'vw_Tasks_all'[TaskPositionID]=3
&&
EARLIER('vw_Tasks_All'[TaskPositionID])=4
)
)

Any help offered appreciated. 

[Anonymous · Accepted solution]

Hi  @ElliotK ,

Here are the steps you can follow：

1. In Power query. Add Column – Index Column – From 1.

2. Create calculated column.

EARLY1 =
var _1=    SUMX(FILTER(ALL(vw_Tasks_all),'vw_Tasks_all'[TaskStatusID]=EARLIER('vw_Tasks_all'[TaskStatusID])&&  'vw_Tasks_all'[eFileID]=EARLIER('vw_Tasks_all'[eFileID])&&'vw_Tasks_all'[Index]=EARLIER('vw_Tasks_all'[Index])),[TaskPositionID])
var _2=    SUMX(FILTER(ALL(vw_Tasks_all),'vw_Tasks_all'[TaskStatusID]=EARLIER('vw_Tasks_all'[TaskStatusID])&& 'vw_Tasks_all'[eFileID]=EARLIER('vw_Tasks_all'[eFileID])&&'vw_Tasks_all'[Index]=EARLIER('vw_Tasks_all'[Index])-1),[TaskPositionID])
    return
IF(
    _1=3&&_2=4,
MAXX(FILTER(ALL(vw_Tasks_all),'vw_Tasks_all'[TaskStatusID]=EARLIER('vw_Tasks_all'[TaskStatusID])&& 'vw_Tasks_all'[eFileID]=EARLIER('vw_Tasks_all'[eFileID])&&'vw_Tasks_all'[Index]=EARLIER('vw_Tasks_all'[Index])-1),[TaskCreationDate]),
    [TaskCreationDate])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ElliotK]

Tried stripping this down to a simplified version :

EARLY2 = CALCULATE(MAX('vw_Tasks_all'[TaskCreationDate]),

FILTER(vw_Tasks_all,'vw_Tasks_all'[eFileID]=EARLIER('vw_Tasks_all'[eFileID]

)))

Getting the below error for the column: EALIER/EARLIEST referes to an earlier row context which doesn't exist.

[amitchandak]

@ElliotK , Can you share the expected output column?

This can give dattime of last task id

EARLY = CALCULATE(MAX('vw_Tasks_all'[TaskCreationDate]),
FILTER(vw_Tasks_all,'vw_Tasks_all'[eFileID]=EARLIER('vw_tasks_all'[eFileID])
&&
'vw_Tasks_All'[TaskCreationDate]>EARLIER('vw_tasks_all'[TaskCreationDate])
&&
vw_Tasks_all,'vw_Tasks_all'[TaskID]< EARLIER('vw_tasks_all'[TaskID]
) ))

[ElliotK]

@amitchandak,

Sure. I am receiving an error stating: Too many arguments were passed to the FILTER function. The maximum argument count for function is 2. 

FYI. I am hoping to see the TaskCreationDate (13/07/2022 10:01) in row ID 2032.

Forgot to mention, this is direct query. Not sure if that matters?