Help with date subtraction with some criteria from different rows

Hi Stanford ,
Thanks for reaching out to Microsoft Fabric Community Forum.

I have reproduced your scenario using Power Query and confirmed the behavior you described. The issue arises when referencing another task’s date, such as "Debug Setup," within the same Job #, particularly if task order varies or certain tasks are absent. To address this, I matched task names within a grouped query (grouped by Job #) instead of using row index. In the grouped table, I created a custom column that retrieves the "Task Must Start Date" for "Debug Setup" and assigns it as the "Task Req’d Date" for "Ready for Parts".

The custom code applied in the grouped step is :

let
    taskTable = [AllTasks],
    debugDate = try Table.SelectRows(taskTable, each [Task Description] = "Debug Setup"){0}[Task Must Start Date] otherwise null,
    addReqdCol = Table.AddColumn(taskTable, "Task Req'd Date", each if [Task Description] = "Ready for Parts" then debugDate else null)
in
    addReqdCol

This method functions for each job, and if "Debug Setup" is not present, the "Task Req’d Date" will remain null as intended.

As shown below, the Task Req’d Date is populated only when "Debug Setup" exists within the same Job #:

Please let me know if you have any questions or need further assistance.

Regards,

Sreeteja.