Forum Discussion
Helper IAudit Plan - Create Start Date from Due Date
Hi Expert,
I am working on an audit plan, and my data set
- Have - Audit Name, Task Name, End Date(Due Date), FieldWork Date
- 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 |
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.
1 Reply
v-jingzhangCommunity Support
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.