Forum Discussion
Solved
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 TaskCreati...
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
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