Audit Plan - Create Start Date from Due Date

[shefalinishad11]

Hi Expert,

I am working on an audit plan, and my data set

1. Have - Audit Name, Task Name, End Date(Due Date), FieldWork Date
2. don't have - Start Date 

Now, every audit has 34 end dates and I need to create a logic from which I can get the start date for that task. Now what I think - Every audit task 2 start date will be the end date of task 1 and task 1 start date will be task 1 due date - 5 days.

I am using the logic below, but it is not working and any help is appreciated.

Start Date =
VAR Project = 'Audit Milestones'[Audit Name]
VAR TaskStartDate = 'Audit Milestones'[Due Date]
VAR nextTaskDate =
MINX(
FILTER('Audit Milestones', 'Audit Milestones'[Audit Name] = Project
&& 'Audit Milestones'[Due Date] > TaskStartDate
// && 'Audit Milestones'[Due Date] > TaskStartDate//
),
aud
)
Return
IF(ISBLANK(nextTaskDate), TaskStartDate, nextTaskDate)

Project Name	Task	Field Work Start Date	Due Date	Start Date ( Logic Required)
P1	T1	25/1/23	12/1/23	T1 Due Date - 5 days
P1	T2	25/1/23	15/1/23	T1 Due Date
P1	T3	25/1/23	20/1/23	T2 Due Date
P1	T4	25/1/23	25/1/23	= Field Work Start Date
P1	T5	10/1/23	25/2/23	T4 Due Date
P2	T1	30/1/23	14/1/23	T1 Due Date - 5 days
P2	T1	30/1/23	24/1/23	T1 Due Date

[v-jingzhang · Accepted solution]

Hi @shefalinishad11 

You can create a new column with this DAX

Start Date = 
var _previousDueDate = MAXX(FILTER('Table', 'Table'[Project Name]=EARLIER('Table'[Project Name]) && 'Table'[Due Date]<EARLIER('Table'[Due Date])),'Table'[Due Date])
return
IF(ISBLANK(_previousDueDate),'Table'[Due Date]-5,_previousDueDate)

I don't know why the start date for P1's Task 4 should be the Field Work Start Date in your example. With above DAX, it still uses previous task's Due Date as the start date. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.