Data Transformation - Create Milestone Start Date column based on data from two other columns

[tomasjezek]

Hello PBI Community Members,

I would like to ask you to help me (and challenge you a bit) with following data transformation task.

I have a data set where there are Projects and their's Milestones - see the picture below. I need to add there two custom Columns which will be based on this logic:

1] add column Project Milestone Index - this one is based on sorted Index column (col F) and mainly based on Project Nr. The idea is to have Index values for each Project.

2] Add column Milestone Start Date with following conditions:

• a. When Project Milestone Index = 0 then use Project stat Date
• b. When Project Milestone Index > 0 use the previous Milestone completion Date of the respective Project Milestone Index 

Plain text data:

Project Nr. Project Start Date Project Milestone Milestone status Milestone Completion Date Index Mileston Start Date Project Milestone Index
Project 1 2/3/2023 M1 Closed 3/2/2023 0
Project 1 2/3/2023 M2 Closed 4/15/2023 1
Project 1 2/3/2023 M3 Closed 8/8/2023 2
Project 1 2/3/2023 M4 Open 3
Project 2 3/6/2023 M1 Closed 3/30/2023 4
Project 2 3/6/2023 M2 Open 5
Project 3 3/7/2023 M1 Closed 3/18/2023 6
Project 3 3/7/2023 M2 Closed 4/12/2023 7
Project 3 3/7/2023 M3 Closed 4/30/2023 8
Project 4 4/14/2023 M1 Closed 4/30/2023 9
Project 4 4/14/2023 M2 Closed 6/26/2023 10
Project 4 4/14/2023 M3 Closed 9/25/2023 11
Project 4 4/14/2023 M4 In process 12

I have tried it in Power Query editor, I was able to do part 1], but then failed.

Desired data state:

Thank you very much in advance for your support and guidance.

Best regards,

Tomas

P.S.:

It is quite similar to this topic

https://community.fabric.microsoft.com/t5/Desktop/Creating-TaskStartDate-Column-Based-on-TaskFinishD...

[HotChilli · Accepted solution]

I used the Index column to make this a bit easier:

ColumnE = 
var _idx = TableK[Index]
var _proj = TableK[PR]
var _offsetDate = CALCULATE(MAX(TableK[Milestone Completion Date]), FILTER(TableK,  TableK[Index] = _idx -1))
VAR _Result = IF ( TableK[Project Milestone] = "M1", TableK[Project Start Date], _offSetDate  )
RETURN
    _Result

[tomasjezek]

Awesome!... I did minor adjustments, but it works perfectly!

Thank you very much. I own you a beer:-D...

Best,

tomas

[HotChilli]

Post that data as text please.

Where is the Project Milestone Index values?  Can you provide the code for that?

PLease show the desired result for Milestone Start Date

[tomasjezek]

Hi,

I updated my post with required information, but I can not provide the code so far.

Maybe there will be a solution without a need to use Project Milestone Index. The main focus is to have Milestone Start Date column.

Thank you.