Forum Discussion
Earlier TaskCreationDate
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.
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
4 Replies
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]
) ))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?
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.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
