Forum Discussion

tomasjezek's avatar
tomasjezek
Frequent Visitor
2 years ago
Solved

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

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-TaskFinishDate/m-p/3581890#M1178172

 

 

Need Help
Show and Tell
  • HotChilli's avatar
    HotChilli
    2 years ago

    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

4 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    2 years ago

    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's avatar
      tomasjezek
      Frequent Visitor
      2 years ago

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

       

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

       

      Best,

      tomas

  • HotChilli's avatar
    HotChilli
    Community Champion
    2 years ago

    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's avatar
      tomasjezek
      Frequent Visitor
      2 years ago

      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.