Help with date subtraction with some criteria from different rows

[Stanford]

Good morning,

This is my extremely slimmed down table in Bi with results wanted.  I manually made the table to hopefully simplify explanation.  However there will be many "Job #s" that all have these same "Task Descriptions".

I am trying to get task req'd date to meet job due date.  Some tasks need to happen before others can start so I will need task must start date as well.  Debug Setup (Row 1) is easy and is just "job due date" minus 9 days for run-off/shipping.  Then the Debug Setup "task must start date" is "Req'd date" minus "task days req'd".

After that is where I am struggling.  Ready for Parts (Row 2) "Task Req'd Date" needs to equal Debug Setup (Row 1) "Task Must Start Date".  Then I can subtract "Days Req'd" from that result.

I cannot figure out how to pull Row 1 "Task Must Start Date" into Row 2 "Task Req'd Date".

Any help would be greatly appreciated.

[Stanford]

Thank you.  I have few meetings this morning, but I will try that today.  I will let you know.

[v-sshirivolu · Accepted solution]

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.

[Stanford]

Any chance you could attach your Bi so I can walk through it to better understand?

[v-sshirivolu]

Hi @Stanford ,

I have reproduced your requirement and implemented the solution. I have attached the PBIX file for your review so you can see the updates. Let me know if you need any changes or further improvements.

Thanks, 

[Stanford]

Good afternoon,

I finally got that to work once I changed my column names.  The auto generated report that ODBC pulls from adds "" to column names.  Formula does not like that.  Thank you all very much.  Now I will just do the same for all the items and done.

[Stanford]

Good morning,  Thank you.  I will try this today.

[Stanford]

Thank you.  I will have a go at it after lunch today and see what I can mess up.

[Stanford]

Good morning,

Not every job will need every one of those tasks.  For example some jobs may not need "Capital Assembly" since machine is being re-used.  Tasks may get shifted around in the table layout for sure, but tasks will need completed in that order if the task is required.  For example: "Prints" will have to be done before "Tooling Assembly" and "Prints" and "PLC Program" will have to be complete before "Ready for Parts" can happen.  Hope that makes sense.

[BA_Pete]

Ok, so we can't directly reference task names then. We can use an index to reference 'previous' task in the project, but will rely on the tasks being ingested/loaded from source in the correct order that they need to be done in (unless you already have a task index column or task order column elsewhere in the table?).

I may not get to look at this today now I'm afraid, sorry. If not tonight, it will be first thing tomorrow morning. However, the basic method I would implement would be as follows, if you wanted to have a go until I can put together a proper solution/example for you:

-1- Group table on [Job #] using the All Rows aggregated column.

-2- Add index column starting from 0 to the nested data table.

-3- Expand required columns back out of nested table.

-4- Use a custom column formula something like this:

// Assuming index column added called "TaskOrder"
Task Req'd Date = try PreviousStepName[Task Must Start Date]{[TaskOrder] - 1} otherwise null

Pete

[Stanford]

Thought I better explain that Row 1 and Row 2 really have nothing to with criteria.  I need the "Task Description" to be what determines what to move and where to move it.  If "Ready for Parts" then "Task Req'd Date" equals "Debug Setup" "Task Must Start Date".

[BA_Pete]

Hi @Stanford ,

Does every project always feature everyone of these tasks or is there a chance a certain project may be missing/not require one of these tasks?

Will these tasks always be required in this exact order, or is there a chance that tasks will get shifted round within the project?

Pete