Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Creating TaskStartDate Column Based on TaskFinishDate

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

Need Help
Show and Tell

4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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.

      ProjectNameProjectIdTaskFinishDateExpected Result - TaskStartDateMilestoneTaskName
      Project-A125-11-202025-11-2020milestone-1B
      Project-A119-01-202426-11-2020milestone-2G
      Project-A128-06-202420-01-2024milestone-3J
      Project-A130-06-202529-06-2024milestone-4E
      Project-A129-08-202501-07-2024milestone-5G
      Project-B220-01-201420-01-2014milestone-1M
      Project-B215-12-201421-01-2014milestone-2U
      Project-B215-12-201416-12-2014milestone-3K
      Project-B209-03-201816-12-2014milestone-4V
      Project-B216-08-202110-03-2018milestone-5A
      Project-C328-09-202128-09-2021milestone-1Q
      Project-C329-12-202329-09-2021milestone-2F
      Project-C330-04-202530-12-2024milestone-3T
      Project-C321-11-202501-05-2025milestone-4T
      Project-C328-02-202622-11-2025milestone-5Q
      Project-C328-02-202601-03-2026milestone-6I
      Project-C321-07-202601-03-2026milestone-7E
      Project-C330-08-202622-07-2026milestone-8H
      Project-C331-08-202631-08-2026milestone-9O
      Project-C331-10-202601-09-2026milestone-10I
      Project-C331-01-202701-11-2026milestone-11F
      Project-C331-03-202701-02-2027milestone-12Y
      Project-C330-06-202701-04-2027milestone-13L
      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        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