Creating TaskStartDate Column Based on TaskFinishDate

[Anonymous]

Hello Power BI Community,

I am currently working on a project and facing a challenge in creating a TaskStartDate column in my Power BI dataset. I have multiple tasks for each project, and I need to sort them based on their finish date. Additionally, I want to select only those tasks where the milestone is not blank.

Here is a brief overview of the requirements:

1. For each project, I need to generate a TaskStartDate column.
2. Tasks without a milestone should be ignored.
3. The TaskStartDate for the first task of each project should be equal to its FinishDate.
4. Subsequent TaskStartDates should be calculated as TaskFinishDate + 1 of the previous task. so the first task of each project cant be calculated so we nee dto keep the same as its finishdate

I have attempted to implement this logic using Power Query, but I am encountering difficulties. Your guidance on how to achieve this in Power BI would be highly appreciated.

Best regards, KT

[Fowmy]

@Anonymous 

Please provide an Excel file with some dummy data and the expected results with examples. You may attach the file link here after saving in Google or One drive.

[Anonymous]

For example: If the task finish date from the first task of one project is 28/1/2023 then start date for the second task of that project will be the next day of the previous task finish date. i.e 29/1/2023

and taskstartdate for for the first task of the project will be same as the finishdate.
below sharing example

This is how data will look when i will remove milestone is blank, mile stone data is bit different i have just given it as milestone-1, 2 but we dont have any such specific order for milestone.

ProjectName	ProjectId	TaskFinishDate	Expected Result - TaskStartDate	Milestone	TaskName
Project-A	1	25-11-2020	25-11-2020	milestone-1	B
Project-A	1	19-01-2024	26-11-2020	milestone-2	G
Project-A	1	28-06-2024	20-01-2024	milestone-3	J
Project-A	1	30-06-2025	29-06-2024	milestone-4	E
Project-A	1	29-08-2025	01-07-2024	milestone-5	G
Project-B	2	20-01-2014	20-01-2014	milestone-1	M
Project-B	2	15-12-2014	21-01-2014	milestone-2	U
Project-B	2	15-12-2014	16-12-2014	milestone-3	K
Project-B	2	09-03-2018	16-12-2014	milestone-4	V
Project-B	2	16-08-2021	10-03-2018	milestone-5	A
Project-C	3	28-09-2021	28-09-2021	milestone-1	Q
Project-C	3	29-12-2023	29-09-2021	milestone-2	F
Project-C	3	30-04-2025	30-12-2024	milestone-3	T
Project-C	3	21-11-2025	01-05-2025	milestone-4	T
Project-C	3	28-02-2026	22-11-2025	milestone-5	Q
Project-C	3	28-02-2026	01-03-2026	milestone-6	I
Project-C	3	21-07-2026	01-03-2026	milestone-7	E
Project-C	3	30-08-2026	22-07-2026	milestone-8	H
Project-C	3	31-08-2026	31-08-2026	milestone-9	O
Project-C	3	31-10-2026	01-09-2026	milestone-10	I
Project-C	3	31-01-2027	01-11-2026	milestone-11	F
Project-C	3	31-03-2027	01-02-2027	milestone-12	Y
Project-C	3	30-06-2027	01-04-2027	milestone-13	L

[Fowmy]

@Anonymous 

Add the following column:

StartDate =
VAR __FirstDate =
    CALCULATE (
        MIN ( Table04[TaskFinishDate] ),
        ALLEXCEPT ( table04, Table04[ProjectName] )
    )
VAR __OffSetDate =
    MINX (
        OFFSET (
            -1,
            ALLSELECTED ( Table04[ProjectName], Table04[TaskName], Table04[TaskFinishDate] ),
            ORDERBY ( Table04[TaskFinishDate], ASC, Table04[TaskName], DESC ),
            PARTITIONBY ( Table04[ProjectName] )
        ),
        Table04[TaskFinishDate]
    )
VAR __Result =
    IF ( Table04[TaskFinishDate] = __FirstDate, __FirstDate, __OffSetDate + 1 )
RETURN
    __Result

[Anonymous]

PARTITIONBY ( Table04[ProjectName] )

I'm getting error on this line for the dax shared above