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...

Need Help

Anonymous

4 years ago

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

amitchandak

Super User

4 years ago

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]
) ))